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.
Table of Contents
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
Recent Comments