 #### Email Us

NITA/TRN/2073

IHRM►C00453

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

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 ## Seeking Microsoft Certification?

No Huff! No Fluff! No Spam!