Forum Discussion
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, "...
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 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
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
