![]() 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.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |