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 with you.

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 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))

Related posts:

How to Open a YAML File in Excel How to make heat map in Excel How to make animated graphs in Excel