Dr. Demento
Well-known Member
- Joined
- Nov 2, 2010
- Messages
- 618
- Office Version
- 2019
- 2016
- Platform
- Windows
I'm looking to create a function that will capitalize names to a rough approximation (I realize I won't capture all last names that have capital letters that are not the first letter, but at the minimum I'd like to capture hyphenation, apostrophe (O'Dell), "McX" variations).
I constructed this code, but all it does is cut off anything to the left of the hyphen/apostrophe when what I want is to capitalize the letter immediately after the designator. The "Mc" part works but the other two just truncate the string. Any thoughts??
Note: this function is acting on an array, if that makes any difference.
Thanks y'all.
I constructed this code, but all it does is cut off anything to the left of the hyphen/apostrophe when what I want is to capitalize the letter immediately after the designator. The "Mc" part works but the other two just truncate the string. Any thoughts??
Note: this function is acting on an array, if that makes any difference.
Thanks y'all.
Code:
Function fx_TEXT_fmt(ByRef rra_data As Variant, _
ByRef icol As Long, _
ByRef ths As Worksheet)
' ~~ Format text into Proper Case for LNAME, FNAME, & INC CITY
Dim irow As Long, _
iCAPS As Long
Dim str_elem As String
' ~~ Loop thru data array
For irow = LBound(rra_data, 1) + 1 To UBound(rra_data, 1)
str_elem = StrConv(rra_data(irow, icol), vbProperCase) ' ~~ Proper case
If str_elem Like "Mc*" Then
str_elem = "Mc" & WorksheetFunction.Proper(Right(str_elem, Len(str_elem) - 2))
ElseIf str_elem Like "D'*" Or str_elem Like "O'*" Then
iCAPS = InStr(str_elem, "'") + 1
str_elem = Replace(str_elem, Mid(str_elem, iCAPS, 1), UCase(Mid(str_elem, iCAPS, 1)), iCAPS, 1, vbTextCompare)
ElseIf str_elem Like "*-*" Then
iCAPS = InStr(str_elem, "-") + 1
str_elem = Replace(str_elem, Mid(str_elem, iCAPS, 1), UCase(Mid(str_elem, iCAPS, 1)), iCAPS, 1, vbTextCompare)
End If
gbl_arr_data_fmt(irow, icol) = str_elem ' ~~ Write updated element back to array
Next irow
End Function