Likewise I cannot think of any use for this formula other than changing last names, although I am sure there are some. I cannot think of any names other than “Mc” or “Mac” which would need this. The formula basically checks for and deconstructs names beginning with “Mc” or “Mac” and then rebuilds them using PROPER to capitalise the second part of the name and reconstructing it. Close with two brackets, one for each IF formula. =IF(LEFT(D3,2)=”mc”,”Mc”&PROPER(RIGHT(D3,LEN(D3)-2)),IF(LEFT(D3,3)=”mac”,”Mac”&PROPER(RIGHT(D3,LEN(D3)-3)),įinally, the false clause for the IFs, use PROPER to capitalise everything else that isn’t a “Mc” or a “Mac”. Next, we use the IF false clause to add a second IF formula to catch “Mac”, this is the same as the above, but for LEFT you need the first 3 letters and when building the second half of the string, RIGHT LEN is minus 3. Finally, you need to capitalise this word, eg Nulty, do this with PROPER… Of the name the first 2 letters are “Mc” the ‘&’ to join with the formula, then use RIGHT to get the second part of the name, with LEN to calculate the number of letters the total length of the word minus 2 (“Mc”). If the left 2 letters = “Mc” then we need to build the last name.

Start with an opening IF and use LEFT to examine the first 2 letters of the name, it does not matter how you express “mc” in the formula, this is not case sensitive… This is probably the simplest approach.įirst, we want to look to see if the name begins with “Mc”. Well, two IF statements, although with some extra work it could be done with one. There are other ways of doing this, I’m sure, but in the UK I think there are pretty much just “Mc”s and “Mac”s which can be taken out quite easily with an IF statement. To fix this problem there are a few different elements required PROPER and the text joining ‘&’ along with IF, LEFT and LEN. Please be sure to read Michael C’s comment reference a problem that can arise from this formula. As was noted the problem using PROPER to correct formatting of last names is that it will convert names that should have two capital letters incorrectly, eg “mcnulty” to “Mcnulty” where it needs to return “McNulty”. In a previous post I looked at the PROPER formula.

Workaround for mac cec