Forum Discussion

SM_Talal's avatar
SM_Talal
Copper Contributor
Jul 04, 2019

Conversion in PROPER Case except the word(s) written in CAPITAL Letter.

Hi, I want to change a sentence written in a single cell to PROPER Case except  the word(s) written in CAPITAL Letter.   For E:g 1, From, "I love APPLE" To, "I Love APPLE"   For E:g 2, From, "...
Excel
Formulas and Functions
Macros and VBA
  • JKPieterse's avatar
    JKPieterse
    Jul 04, 2019

    SM_Talal This User defined function (in a normal module in VBA) seems to do it:

    Option Explicit

Function MyProper(Source As Variant) As String
    Dim Data As Variant
    Dim lWd As Long
    Dim Words As Variant
    If TypeName(Source) = "Range" Then
        Data = Source.Value
    Else
        Data = Source
    End If
    Words = Split(Data, " ")
    For lWd = LBound(Words, 1) To UBound(Words, 1)
        If Words(lWd) <> UCase(Words(lWd)) Then
        Words(lWd) = Application.Proper(Words(lWd))
        End If
    Next
    MyProper = Trim(Join(Words, " "))
End Function
JKPieterse's avatar
JKPieterse
Silver Contributor

SM_Talal In that case, update the function to:

Function MyProper(Source As Variant) As String
    Dim Data As Variant
    Dim lWd As Long
    Dim Words As Variant
    If TypeName(Source) = "Range" Then
        Data = Source.Value
    Else
        Data = Source
    End If
    Words = Split(Data, " ")
    For lWd = LBound(Words, 1) To UBound(Words, 1)
        If Words(lWd) <> UCase(Words(lWd)) And Not Isnumeric(Left(Words(lWd),1)) Then
            Words(lWd) = Application.Proper(Words(lWd))
        End If
    Next
    MyProper = Trim(Join(Words, " "))
End Function
dwcartwright1's avatar
dwcartwright1
Copper Contributor
Oct 09, 2019

JKPieterse I may have a problem to up the ante a bit more.  I've been looking through the forums for a function that helps me turn all caps text input into a Proper string, with some exceptions.  If one of the words is a specific abbreviation ("CR" or "SR" or "IH" or "US"), then I need that word to stay all caps.  If a word begins with a numeric (1TH, 2ND, 3RD, 4TH, etc.), then I need it to switch to lower case.

 

Some examples:

  From: "EAST 2ND STREET"  To: "East 2nd Street"

  From: "IH 59 SOUNDBOUND"  To: "IH 59 Southbound" 

  From: "BROWN CREEK ROAD"  To: "Brown Creek Road"

 

I feel like I know what needs to be done, but I don't think I'm getting the syntax right.

  • JKPieterse's avatar
    JKPieterse
    Silver Contributor
    Oct 09, 2019
    Please post the code you have so we might advise.
    • dwcartwright1's avatar
      dwcartwright1
      Copper Contributor
      Oct 09, 2019

      I think I just came up with something workable (albeit very inefficient)...

       

      Function MyProper(Source As Variant) As String

          Dim Data As Variant

          Dim lWd As Long

          Dim Words As Variant

          If TypeName(Source) = "Range" Then

              Data = Source.Value

          Else

              Data = Source

          End If

          Words = Split(Data, " ")

          For lWd = LBound(Words, 1) To UBound(Words, 1)

              If IsNumeric(Left(Words(lWd), 1)) And Right(Words(lWd), 2) = "ST" Then

                  Words(lWd) = LCase(Words(lWd))

              ElseIf IsNumeric(Left(Words(lWd), 1)) And Right(Words(lWd), 2) = "ND" Then

                  Words(lWd) = LCase(Words(lWd))

              ElseIf IsNumeric(Left(Words(lWd), 1)) And Right(Words(lWd), 2) = "RD" Then

                  Words(lWd) = LCase(Words(lWd))

              ElseIf IsNumeric(Left(Words(lWd), 1)) And Right(Words(lWd), 2) = "TH" Then

                  Words(lWd) = LCase(Words(lWd))

              ElseIf Words(lWd) = "CR" Then

                  Words(lWd) = UCase(Words(lWd))

              ElseIf Words(lWd) = "US" Then

                  Words(lWd) = UCase(Words(lWd))

              ElseIf Words(lWd) = "SR" Then

                  Words(lWd) = UCase(Words(lWd))

              ElseIf Words(lWd) = "IH" Then

                  Words(lWd) = UCase(Words(lWd))

              Else

                  Words(lWd) = Application.Proper(Words(lWd))

              End If

          Next

          MyProper = Trim(Join(Words, " "))

      End Function

      • JKPieterse's avatar
        JKPieterse
        Silver Contributor
        Oct 09, 2019
        Not bad at all. Could be slightly improved by moving the isnumeric test into a separate if statement under which you nest all related other tests.

Resources