There were many times when those initials made me crazy. In this article, you can teach yourself how to handle initials with Excel. A few tricks for you here.
A few tricks for you on how to handle initials within Excel. All of them are combined in this article.
Getting initials from a name
Let's start with the easiest one. When only two-lettered initials are needed, just use:
=LEFT($A1)&MID($A1,FIND(" ",$A1)+1,1)
This formula will give you the initials of the first name and surname. Two letters only.
Getting initials from a first name, middle name, and surname
More advanced one. Use this one when the first name, middle name, and surname are always provided. Below, one will help you:
=LEFT($A1)&MID($A1,FIND(" ",$A1)+1,1)&MID(RIGHT($A1,LEN($A1)-FIND(" ",$A1,1)),FIND(" ",RIGHT(,LEN($A1)-FIND(" ",$A1,1)))+1,1)
This formula gives you three initial letters. It will not work when there is no middle name provided.
Getting initials from a full name
This is a complex formula which you can use to extract initials no matter what exactly you have in your data.
=IF(LEN($A1)-LEN(SUBSTITUTE($A1," ",""))=0,LEFT($A1,1),IF(LEN($A1)-LEN(SUBSTITUTE($A1," ",""))=1,LEFT($A1,1)&MID($A1,FIND(" ",$A1)+1,1),LEFT($A1,1)&MID($A1,FIND(" ",$A1)+1,1)&MID($A1,FIND(" ",$A1,FIND(" ",$A1)+1)+1,1)))
This formula is complicated but works every time.
Getting the first name and surname (without a middle name)
Use the below formula when you don't care about middle names.
=LEFT($A1,SEARCH(" ",$A1))&RIGHT($A1,LEN($A1)-SEARCH(" ",$A1,SEARCH(" ",$A1)+1))
This formula simplifies your data and cuts off middle names.
Changing order of first name, middle name and surname
Thanks to this formula, you can change the order. In my example, you will get: surname coma space first name initial of middle name
=RIGHT($A1,LEN(G2)-FIND(".",$A1)-1)&", "&LEFT($A1,FIND(".",$A1))
I hope this article makes your life easier. Thanks to it, you will handle names and initials easily.