Name Editing in Excel

K

KCT

Registered User
Messages
90
Is it possible to edit a list of names in excel so that the Mc's have a upr case letter for the rest of the name e.g. McShane instead of Mcshane. I've tried using ASAP utilities but I can't get it to do this task.

Thanks
 
Ive grappled with this and related issues but with no success Im afraid.

Ive provided the people who enter and maintain the data with explicit data cleaning guidlines and have explicitly told them how to deal with Mac's, O's, double barrelled names, names in Gaelic(O, Ni, O hAlpin etc), foreign language names. After some initial resistance the system works well now.
 
try;
=IF(ISERROR(FIND("Mac", D1, 1)) = FALSE, LEFT(D1, LEN("Mac")) & UPPER(MID(D1, LEN("Mac")+1, 1)) & RIGHT(D1, LEN(D1)- LEN("Mac")-1), D1)

Where your data is in column 'D'
This will cover all 'Mac's replace 'mac' with 'mc' to do 'mc's
For spaces between Mac and surname, and one appropriate places.
You should be able to clean up most of your data with these combinations.

Other than that, you'll probably have to write a macro.
 
Not very elegant but this fomula should work for a string in A1. =IF(EXACT(LEFT(A1,2),"Mc"),CONCATENATE("Mc",PROPER(MID(A1,3,999))),A1) To explain, EXACT returns true if 2 strings match exactly, LEFT(string,2) returns the first 2 chars of string, MID(string,3,999) returns chars from 3 to 999 of a string, PROPER(string), capitalises the first letter of a string, CONCATENATE adds 2 strings together.
 
What if someone's surname is greater than 999 characters? :D
 
Bigger problems than that I'm afraid. Neither example will cater for input in all lower case. One works for "Mc", the other for "Mac", neither for both.
 
Neither example will cater for input in all lower case. One works for "Mc", the other for "Mac", neither for both.
Click to expand...

Well spotted. I should have used the 'SEARCH' function instead, which isn't case sensitive. The following does both Mc and Mac;

=IF(ISERROR(SEARCH("Mac",D1,1))=FALSE,LEFT(D1,LEN("Mac"))&UPPER(MID(D1,LEN("Mac")+1,1))&RIGHT(D1,LEN(D1)-LEN("Mac")-1),IF(ISERROR(SEARCH("Mc",D1,1))=FALSE,LEFT(D1,LEN("Mc"))&UPPER(MID(D1,LEN("Mc")+1,1))&RIGHT(D1,LEN(D1)-LEN("Mc")-1),D1))

Or you could get someone looking for a few bob to manually go through the list.
 
umop3p!sdn said:
Or you could get someone looking for a few bob to manually go through the list.
Click to expand...

My list contained the names of 10,000 (mostly Irish) people. The list was constantly being updated and changed. Initially I had formulae and macros but eventually had to concede that manually inputting the data using a policy was the more effective.
 
I don't know if its suitable but if you could split the names into 2 cells, so the first cell would be Mac and the next cell would be Shane, this will allow you to control the S being a capital letter a bit better.
Then you could concatanate (sic) the cells to bring back the names into once cell with correct capitalisation
 
You must log in or register to reply here.
Back
Top