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

Using PowerQuery

Use below M Code by Owen Price

Or You use custom column by example as shown below

Resources:

Check more alternatives in this Linkedin Post

DOWNLOAD WORKSHEET

Do you want to excel in Excel?

Or

Seeking Microsoft Certification?

Every Week Receive Resources To Get you Closer to Your Goal!

No Huff! No Fluff! No Spam!

You are on your way to Excelling in Excel and Becoming Microsoft Certified!

Share This

Spread the Good News!

Do me favour, please? Share this with your friends!

Thanks For Commenting!

Awesome for you to comment! Please share the post with your friends.