Capitalize One Letter in Text String

Posted by JAF on May 17, 2001 3:58 AM

Hiya

I have a series of text strings in which I want to capitalise a specific letter, but leave the rest of the string intact.

Havign converted a list of customer names from upper case using the Proper function, anyone whose name contains either Mac or Mc comes out with the subsequent letter in lower case (e.g. Mcleod) when I want the letter following Mac or Mc to be capitalised (e.g. McLeod).

I can identify where in each text string the Mac or Mc appears by using =FIND, and I want to capitalise the letter that appears 3 after Mac and 2 after Mc.

Is there any way using VBA code to capitalise a specific letter in a text string and leave all others intact?

JAF

Posted by Dave Hawley on May 17, 2001 4:24 AM

Hi JAF

How about:

=IF(LEFT(A5,3)="mac","Mac" & PROPER(RIGHT(A5,LEN(A5)-3)), IF(LEFT(A5,2)="mc", "Mc" & PROPER(RIGHT(A5, LEN(A5)-2)), PROPER(A5)))

Dave

OzGrid Business Applications

Posted by Dave Hawley on May 17, 2001 4:30 AM

To do this via VBA, use:

Sub CodeWay()
Dim rListRange As Range

Set rListRange = Range("A1", Range("A65536").End(xlUp))
rListRange.Offset(0, 1).FormulaR1C1 = _
"=IF(LEFT(RC[-1],3)=""mac"",""Mac""&PROPER" _
& "(RIGHT(RC[-1],LEN(RC[-1])-3)),IF(LEFT(RC[-1],2)" _
& "=""mc"",""Mc"" & PROPER(RIGHT(RC[-1],LEN(RC[-1])-2))" _
& ",PROPER(RC[-1])))"

rListRange.Offset(0, 1).Copy
Range("A1").PasteSpecial xlPasteValues
rListRange.Offset(0, 1).Clear
Application.CutCopyMode = False
End Sub

This particular case is for the surnames being in Column A starting from A1.

Dave

OzGrid Business Applications