Replace a single character after a delimiter

Dr. Demento

Well-known Member
Joined
Nov 2, 2010
Messages
618
Office Version
  1. 2019
  2. 2016
Platform
  1. 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.

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
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
I'd suggest try the PROPER function first before trying anything else. PROPER converts o'brian to O'Brian and rev-not to Rev-Not.
 
Upvote 0
I tried Proper before, but I guess I didn't understand the syntax well enough. I'll give it another go.

Thanks much
 
Upvote 0

Forum statistics

Threads
1,224,836
Messages
6,181,250
Members
453,026
Latest member
cknader

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top