SOLVED

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

Copper Contributor

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, "united arab emirates (UAE)" To, "United Arab Emirates (UAE)"

10 Replies
best response confirmed by SM_Talal (Copper Contributor)
Solution

@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

@SM_Talal

 

Hi,

 

I'll show you the easiest way I found to do so!

Please check out this video:

https://1drv.ms/v/s!ArtU2vr1IWYYhLsK1YfN0Kdj2f-CQg?e=Q40WSS

 

Hope that helps

 

Thanks a lot Haytham Amairah ..

@Jan Karel Pieterse 

Hi Jan,

This really helped me a lot, now I want to increase the difficulty level little bit.

For E:g,

From: "I got 4th position in grade EIGHT" To: "I Got 4th Position In Grade EIGHT"

 

While applying PROPER "4th" will become "4Th" which in incorrect.

 

Here we have to restrict a capital letter after a number, Like 1st, 2nd, 3rd,4th etc.

 

Thank you once again. :)

@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

@Jan Karel Pieterse 

 

Jan,

 

Thank you very much .. That's very great .. I cannot explain..

God Bless You.

@Jan Karel Pieterse 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.

Please post the code you have so we might advise.

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

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.
1 best response

Accepted Solutions
best response confirmed by SM_Talal (Copper Contributor)
Solution

@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

View solution in original post