Many times, I found myself struggling with handling initials. In this article, you can learn how to manage initials with Excel. I’ll share a few tricks for handling initials using Excel formulas.
Here are some useful tricks for handling initials within Excel, all compiled in this article.
Extracting Initials from a Name
Let’s start with a straightforward task: obtaining two-letter initials using this formula:
=LEFT($A1)&MID($A1,FIND(” “,$A1)+1,1)
This formula retrieves the first name and surname initials, generating a two-letter combination.
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 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))
Whether you’re dealing with simple first and last names or more complex full names with middle initials, the formulas shared in this article will help streamline your process.
