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 FunctionJKPieterse 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.
