Split full name into parts

In this example, the goal is to split the names in column B into three separate parts (First, Middle, and Last) with a single formula. In cases where there is no middle name, the Middle column should be blank. In cases where there are two middle names, the Middle column should contain both names.

The challenge

The main challenge is that a middle name is not always present and in some cases, there is more than one middle name. As a result, when TEXTSPLIT (or Excel's older Text to Columns feature) is used to split the names, the various parts of each name do not always fall into the right column, as you can see in the worksheet below:

The output is only correct when a name has exactly three parts.

Separate formulas

One way to approach this challenge is to parse each part of the name in three separate formulas:

• Get first name from name
• Get last name from name
• Get middle name from name

See the links above for details. However, it is also possible to create an all-in-one formula to parse each name in one step with the LET function. This is the approach explained below. 

All-in-one formula

The solution below shows how the LET function can help organize what would otherwise be a complex formula. The formula in cell D5, copied down, looks like this:

=LET(
  parts,TEXTSPLIT(B5," "),
  count,COUNTA(parts),
  first,INDEX(parts,1),
  last,INDEX(parts,count),
  IFS(
    count=1,HSTACK(first,"",""),
    count=2,HSTACK(first,"",last),
    count>2,HSTACK(first,TEXTJOIN(" ",1,DROP(DROP(parts,,1),,-1)),last)
   )
)

At the top level, the LET function is used to define four variables: parts, count, first, and last. The variable parts is assigned a value by the TEXTSPLIT function:

=TEXTSPLIT(B5, " ") // split name into separate parts

The delimiter given to TEXTSPLIT is a single space (" "), so the result is an array that contains all parts (i.e. words) that appear in the full name. Next, the COUNTA function is used to assign a value to count:

=COUNTA(parts) // count parts

Next, we use the INDEX function to assign values to first and last. To get a value for first, we ask for the first value in parts. To get a value for last, we ask for the last value in parts, by using the count. The code looks like this:

INDEX(parts,1) // first
INDEX(parts,count) // last

Note: the CHOOSECOLS function would also work instead of INDEX, but INDEX is simple and brief, so we use it here.

At this point, we have what we need to create the final output for each name. What we want in the end is an array that contains three values for each name: first, middle, and last. This requires some conditional logic, which we implement with the IFS function:

IFS(
  count=1,HSTACK(first,"",""),
  count=2,HSTACK(first,"",last),
  count>2,HSTACK(first,TEXTJOIN(" ",1,DROP(DROP(parts,,1),,-1)),last)
)

If the count is 1, we use the HSTACK function to assemble a horizontal array that contains only the first name (first):

HSTACK(first,"","")

This allows the formula to handle a case where there is only a single name (e.g. Bono, Prince, Madonna, Adele, Beyoncé, etc.). Note that we provide an empty string ("") for the middle name and the last name so that we are always returning an array with three items on each row.

Next, if the count is 2, we use HSTACK to return the first name and the last name, and provide an empty string for the middle name:

HSTACK(first,"",last)

Finally, if the count is greater than 2, we use HSTACK again like this:

HSTACK(first,TEXTJOIN(" ",1,DROP(DROP(parts,,1),,-1)),last)

This is the trickiest part of this formula. Note that we supply the first name and the last name in a normal fashion. For the middle name, however, we use the TEXTJOIN function and the DROP function like this:

TEXTJOIN(" ",1,DROP(DROP(parts,,1),,-1))

Our goal here is to keep all middle names together and return just a single value. We don't want to output additional cells for names that have more than one middle name. We start by using the DROP function twice:

DROP(DROP(parts,,1),,-1)

The inner drop function removes the first name from parts (i.e. the first value in the parts array). The outer DROP function removes the last name from parts. The TEXTJOIN function then concatenates the remaining values in parts, each value separated by a single space. The result is returned to HSTACK as the middle name. The practical effect of this fancy code is that the formula can handle any number of middle names: all names that appear between the first name and the last name are simply joined together.

Note: the approach in this formula works fine for the data as shown, but you might need to adjust the logic in the formula to suit your data.

Conclusion

Splitting full names into separate parts (first, last, and middle) is challenging because names have a variable number of parts. You can use the TEXTSPLIT function to easily split names into separate parts, but the names might not land in the correct column. The same problem occurs if you use Excel's Text-To-Columns feature. One way to solve this problem is to create a formula that handles each name differently depending on how many parts it contains. The LET function is ideal for this purpose because it can be used to define variables that are then processed with conditional logic in a single formula.