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

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
    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
    • SM_Talal's avatar
      SM_Talal
      Copper Contributor
      Jul 05, 2019

      JKPieterse 

      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. :)

      • JKPieterse's avatar
        JKPieterse
        Silver Contributor
        Jul 05, 2019

        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

Resources