Replace a single character after a delimiter

Dr. Demento

Well-known Member
Joined
Nov 2, 2010
Messages
575
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
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

yky

Well-known Member
Joined
Jun 7, 2011
Messages
1,833
Office Version
  1. 2010
Platform
  1. Windows
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.
 

Dr. Demento

Well-known Member
Joined
Nov 2, 2010
Messages
575
I tried Proper before, but I guess I didn't understand the syntax well enough. I'll give it another go.

Thanks much
 

yky

Well-known Member
Joined
Jun 7, 2011
Messages
1,833
Office Version
  1. 2010
Platform
  1. Windows
You're welcome. Glad I can help.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,199
Messages
5,623,323
Members
415,966
Latest member
ctorohuamanchumo

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
Top