Hi - Dave here.

Happy Friday!

I've got another fun formula for you :)

In the worksheet below, the goal is to split different parts of a name (First, Middle, and Last) into the right columns with a single formula.

The main challenge is that a middle name is not always available, so names can end up in the wrong columns.

The solution in cell D5 is based on the LET function plus several other fancy new functions:

=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)
   )
)



[Download the workbook and read the full explanation]

The beauty of this formula is that will correctly handle names with one part, with two parts, and with three or more parts. The key is the LET function, which makes it possible to assign variables in Excel formulas and then use those variables to make the right choices. It's a good example of the future of more advanced formulas in Excel.

For now, this formula will only work in Excel 365, since it requires new functions that are only available in the latest version. However, the article above provides links to other formulas for first, middle, and last names which will work in older versions of Excel.

Correction

In last week's email, I managed to confuse left and right in my description of how instance numbers work in TEXTAFTER and TEXTBEFORE. What I should have said is this:

The cool thing in the example is the negative instance number inside TEXTAFTER. Positive instance numbers count from the left, while negative numbers count from the right. So, the -1 in this case tells TEXTAFTER to get all text after the last space. This is an excellent feature, useful in many situations.

This is also fixed in the article itself.

Excel formulas

We maintain a list of over 500 working formulas here.

If you need more structure, we also offer video training.

Have a great weekend!

Dave

Exceljet
P.O. Box 4804
Salt Lake City, UT 84110

Copyright © 2023 Exceljet, All rights reserved.
You received this email because you are subscribed to our newsletter.
To unsubscribe, click the link below.