Refine Proper Case Macro

  • I am using this macro to ensure that a range of cells appear in Proper Case.

    However I am encountering a drawback, sometimes I have text which I want in Upper Case but which is changed into Proper Case. I was wondering if there was a way to work around this.

    Example: Practical W/W appears as W/w or Woodturing (GMC) appears as Woodtrunign(gmc)

  • Re: Refine Proper Case Macro

    Two ways:

    1.) You could make the left-character Upper and the right characters Lower. You might also have to use something like WorksheetFunction.Find or InStr to determine certain characters, depending on what you've got in your cell references.

    2.) Or do something like this for just proper case:

    Sub ProperCase()
    Dim s$
    s = Range("a2").Value
    s = WorksheetFunction.Proper(s)
    MsgBox s
    End Sub

    So whatever you put in cell A2 has the first letter capitalized in the MessageBox.

  • Re: Refine Proper Case Macro

    If I understand correctly, you want certain things (like accromnyms and some abreviations) to stay all upper case while everything else is converted to Proper case. IF you starting text is generally lower case, then you could parse the string in each cell into space-separated components and check to see if the ASCI value of each character equals the ASCI value of it UCASE equivalent. If they all are equal, then this is an ALL-CAPS entry and don't set it to proper. Set components that don't meet this criteria to Propper Case. (That is, test each parsed space-separated component separately and set each component to Proper -- or not -- individually. Then concatentate the pieces back together.

    EDIT (forgot the code to test each character). Once you've parsed the space-spearated components, use something like this, which checks the 2nd character, to see if it is in UPPER case.



    If your initial text is in ALL CAPS to strat with, I don't see a way to tell in code what should or should not stay UPPER CASE.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!