EXCEL - Capitalising the first letter after 'Mc' in a surname.

[postmanphat]

Hi,

I've got a spreadsheet with a surname field which contains a lot of Scottish names like 'Mcmanus', I need these to be formatted 'McManus'.

On a related note, there are also a few hyphenated names where the first letter after the hyphen is in lower case but needs to be in upper case.

Any help would be greatly appreciated!

Thanks

Dave

 

[xlbo]

The 1st part:
=IF(LEFT(PROPER(A1),2)="Mc",PROPER(LEFT(A1,2))&PROPER(RIGHT(A1,LEN(A1)-2)),Proper(A1))

The 2nd part is handled by the PROPER function - any letter after a hyphen is capitalised so the formula above shouold work for both your issues



Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[yogia]

Hi Dave:

How about ...

=IF(LEFT(A1,2)="Mc","Mc"&PROPER(MID(A1,3,255)),PROPER(A1))

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[postmanphat]

Cheers guys... XLBOs answer did the trick for both problems as he correctly alluded to.

Thanks again.