How to Write Name with Initials

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.

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

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

Changing order


I hope this article makes your life easier. Thanks to it, you will handle names and initials easily.