How To Initialize the First and/or Second Name in Excel

Here are 4 ways to Initialize the First and/or Second Name in Excel

This article is inspired Bhavya Gupta Youtube Video.

He elegantly solves the issue using LAMBDA function but I will try to solve solve it using normal TEXT functions.

Using REPLACE & FIND Function

=REPLACE(A2,1,
IFERROR(FIND(” “, A2, FIND(” “, A2) + 1),FIND(” “, A2)),
LEFT(A2, 1) & ” .” &
IF(LEN(A2) – LEN(SUBSTITUTE(A2,” “,””)) = 1, “”,
MID(A2,FIND(” “, A2)+1,1 ) & ” .” ))

Here is how it works:

FIND(” “, A2, FIND(” “, A2) + 1) ► finds the second space in a name and returns its position or an error in case of a single space

Use the IFERROR function to return the position of a single space ► FIND(” “, A2)

The position returned by the above functions tells the REPLACE function how many characters to replace i.e. All characters up to 2nd space or 1st space

LEFT(A2, 1) ► initializes the first name

Since there are staff with only 2 names, you need to check if there is a need to initialize the second name

LEN(A2) – LEN(SUBSTITUTE(A2,” “,””)) ► checks if the name has 1 or 2 spaces

IF(LEN(A2) – LEN(SUBSTITUTE(A2,” “,””)) = 1, “” ► If only 1 space is found ignore initializing the second name

Else, MID(A2,FIND(” “, A2)+1,1 ) ► return the 1st letter after the second space

Rick Rothstein on Linkedin gave a simplified version on above function as shown below

=LEFT(A2)&"."&
IF(COUNTIF(A2,"* * *"),MID(A2,FIND(" ",A2),2)&". "," ")
&REPLACE(A2,1,
FIND(" ",A2,FIND(" ",A2)+COUNTIF(A2,"* * *")),
"")

The most intriguing part of the function is the COUNTIF part

COUNTIF(A2,”* * *”) ► checks if the string has 3 parts separated by spaces. If TRUE, returns 1 else 0

Using LAMBDA Function

Inspired by Peter Bartholomew on Linkedin here is the LAMBDA ALternative

=LAMBDA(string,
   LET(
    split, TEXTSPLIT(string, " "),
    inits, LEFT(DROP(split, , -1)),
    surname, TAKE(split, , -1),
    TEXTJOIN(". ",, inits, surname)
     )
)

Here is How it works