How to make initials from a name with TEXTAFTER
The first letter of each name, joined together, without typing them out by hand.
The formula
=LEFT(A2,1)&LEFT(TEXTAFTER(A2," "),1) in column B
What each part does
LEFT(A2,1)- The first letter of the first name.
TEXTAFTER(A2," ")- Everything after the first space, which is the surname.
&- Joins the two letters together.
How it works, and what to watch for
The formula assumes two names separated by one space. A middle name is picked up as part of the surname, so the second initial comes from the middle name instead.
For a name with a middle name, splitting on the last space is safer, which takes a longer formula built around TEXTAFTER with a nested SUBSTITUTE.
TEXTAFTER needs Microsoft 365. On earlier versions, MID(A2,FIND(" ",A2)+1,1) picks the same letter.
Questions
What if the name is one word?
TEXTAFTER returns #N/A because there is no space. Wrapping it in IFERROR gives just the first initial.
How do I add a full stop between them?
Join it in the middle: LEFT(A2,1)&"."&LEFT(TEXTAFTER(A2," "),1)&".".
Can I do three initials?
Yes, by nesting TEXTAFTER inside itself to reach past the second space, though at that point splitting the name into columns first is easier to read.
The example sheet
| Name |
Initials |
| Emma Wilson |
|
| Thomas Klein |
|
| Lena Fischer |
|
If this helped, these are close by
More on TEXTAFTER, and every walkthrough that uses it.
