In episode 52, I talked about how to extract text from a cell. You might use this formula to extract a certain value from a cell when your database or CSV export contains a bunch of miscellaneous prefix data. This formula relies on finding a certain character in the cell and then using the MID formula to get the data you need. Turns out this formula doesn't solve all use cases. In particular, when it comes to getting first names, middle name(s), and last names (surnames) from a cell. Evert Scholtz left the following comment on episode 52's YouTube tutorial:

You're in luck Evert! While the formula discussed in this post won't account for salutations, the solution should get you on your way to getting salutations to work too. This formula trick allows you to extract the first name, middle name(s), and last name from a cell where there are multiple spaces in between names. The reason I like the formula (in particular for the middle names) is because it builds on the formula from episode 52, the previous episode on counting the number of words in a cell, and the idea that formulas are composable (also discussed in the previous episode). Link to the Google Sheet for this episode is here.

Watch a tutorial of this episode:

https://youtu.be/d2CPXF1CtdE

Formulas for first name, middle name(s) and last name

If you just want to see the formulas, take a look below. The formulas assume your data starts in cell A2 with the output looking like this (make a copy of the Google Sheet to use formulas directly):

Formula to extract the first name

=LEFT(A2,FIND(" ",A2)-1)

Formula to extract middle name(s)

=IF(LEN(A2)-LEN(SUBSTITUTE(A2," ",""))=1,"",MID(A2,FIND(" ",A2)+1,FIND("#",SUBSTITUTE(A2," ","#",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))-FIND(" ",A2)-1))

Formula to extract last name

=RIGHT(A2,LEN(A2)-FIND("#",SUBSTITUTE(A2," ","#",LEN(A2)-LEN(SUBSTITUTE(A2," ","")))))

As with all my posts, I like to dig into why and how these formulas work, so read on if you want to to see the step-by-step tutorial. The second sheet on the Google Sheet also contains the intermediate steps to get to these formulas so you can see formula composability at its finest.

Creating intermediate columns to test your formulas

I didn't experiment in one cell to get any of the above formulas (especially the formula for extracting middle names). A common practice is to create what I call "intermediate" columns to test out the final formula you want to use.

Columns in Google Sheets and Excel are "cheap." They cost nothing to create, and give your colleague or end user the ability to see how you came to your solution. Typically you would hide these intermediate columns from the final output so that it doesn't clutter your report,