Here, we will learn how to create name abbreviation using the Excel TEXTSPLIT, LEFT and CONCAT functions in one formula.
The dataset (shown in the image) contains a list of full names in column A. Some contain first and last names and some contain first, second and last names. The goal is to extract one character from the left side of first, second and last name so that a name abbreviation is created. To accomplish the task, we create the helper column "Abbreviation" in column B and use TEXTSPLIT, LEFT and CONCAT functions in one formula.
The formula in B3, copied down, is:
=CONCAT(LEFT(TEXTSPLIT(A3," "),1))
How this formula works
TEXTSPLIT splits the text "Daniel Wilson Anderson" into separate values into columns using the delimiter " " (space). LEFT extracts one character from the left side of each value "Daniel", "Wilson" and "Anderson" and returns "D", "W" and "A" into columns. CONCAT joins the values "D", "W" and "A" into one single value. Therefore, we get the name abbreviation "DWA".
You have successfully learnt how to create name abbreviation using the Excel TEXTSPLIT, LEFT and CONCAT functions in one formula! I hope this post helped you.
