Initials from Names using Excel Formulas
Often when you are processing customer records or doing mail merge, it might be useful to get initials from a given name, like JFK for John F Kennedy.
You can do this using simple text formulas (left(), mid(), find()) combined with if(). Here is how:
Assuming cell B3 has the full name, then this is the formula you can use to get the Initials:
=if(len(B3)-len(SUBSTITUTE(B3," ",""))=0,left(B3,1),if(len(B3)-len(SUBSTITUTE(B3," ",""))=1,left(B3,1)&mid(B3,find(" ",B3)+1,1),left(B3,1)&mid(B3,find(" ",B3)+1,1)&mid(B3,find(" ",B3,find(" ",B3)+1)+1,1)))
As you can see, I have used different logic to find initials, based on the number of spaces in the name.
For the sake of simplicity I have limited the formula for names with three, two and one part only (ie first name, middle name and last name), for some reason if the name has more than 3 parts, then this formula would result in initials for the first three chunks of the name. See the example on google docs.
Leave a Reply
|Introducing new design of Pointy Haired Dilbert||6 charts you will see in hell|