How to Write Name with Initials

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.

Two letter initials

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.

How to handle names and initials

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.

Name without middle name

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

See also  How to open dbf file in Excel?

=RIGHT($A1,LEN(G2)-FIND(“.”,$A1)-1)&”, “&LEFT($A1,FIND(“.”,$A1))

Changing order