Recently, I was given a bunch of people’s full names in a list format. However, I wanted to change it to display their first and last names only. This wouldn’t have been so difficult if the names were given to me in [first middle last] format. However, they were given to me in [last, first middle] format. So, I’ve decided to help anyone who needs these kinds of formulas. I’ll walk you through my thinking process while coming up with these formulas. Note that they might need to be adjusted to suit your own needs. Here is my example document if you’d like to see how I did it, and here is how I set up the spreadsheet:
| B | D | E | F | H | |
| 2 | raw name | first name | middle name | last name | =D2&” “&F2 |
Problem Solving – Last Name
Let’s say we were given the name Billy Bob Joe. His name in the raw paste data column would be given to me like this: Joe, Billy Bob. I started off by picking the easiest part of the name to extract first: the last name. My thinking was that I could just write a formula to take everything left of the comma and call that the last name. I will put the raw name in the cell B2.
=LEFT(B2,FIND(“,”, B2, 1)–1)
Formula Explanation
LEFT: =LEFT(string, [number_of_characters]) LEFT will output everything from the beginning of the string until the specified number of characters. The number_of_characters is in square brackets, meaning that it’s optional, and defaults to 1. For those of you that have coded before, there is no zero offset, meaning that if you input =LEFT(“Bob”, 1), it will return a B, rather than the o. FIND: =FIND(search_for, text_to_search, [starting_at]) FIND will output the position at which search_for first appears in text_to_search. It travels from left to right. starting_at is the first character in text_to_search that it will begin to search and defaults to 1. Similar to LEFT, there is no zero offset. The formula that I’ve written to extract the last name from the raw paste data finds the comma and gets everything to the left of it.
Problem Solving – First Name
The next step is to get the first name. The way I would extract the first name from the raw paste data is to grab the word immediately to the right of the comma. With this method, I will assume that all first names are one word. Again, the raw name is in the cell B2.
=LEFT(TRIM(RIGHT(B2,LEN(B2)–FIND(“,”,B2))),FIND(” “,TRIM(RIGHT(B2,LEN(B2)–FIND(“,”,B2&” “)))&” “)–1)
This is significantly more bulky than extracting last names.
Formula Explanation
RIGHT: =RIGHT(string, [number_of_characters]) Right performs the opposite as left. It will extract number_of_characters from the right side, again, without the zero offset. TRIM: =TRIM(text) Trim will remove any extra spaces in the text. Extra spaces refers to spaces at the beginning of the text, end of the text, or repeated spaces within the text. LEN: =LEN(text) LEN will output the length (number of characters) in the text. Breaking down the formula, it’s basically a giant LEFT statement. Remember that LEFT’s syntax is LEFT(string, [number_of_characters]). The string in this formula is TRIM(RIGHT(B2,LEN(B2)–FIND(“,”,B2)) which extracts everything from the right of the comma. number_of_characters is FIND(” “,TRIM(RIGHT(B2,LEN(B2)–FIND(“,”,B2&” “)))&” “)–1 which will find the space and tell us how many letters the first name is to extract. Notice at the end, I put &” “ which will add a space to the end of the thing it’s checking for a space. This way, if there is no middle name, and no space exists after the first word after the comma, it won’t return an error.
Problem Solving – Middle Name
I thought that extracting middle names would be the hardest. However, I really liked the way I solved the problem: Again, the raw name is in the cell B2, the last name is in F2, and the first name is in D2.
=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2,F2,“”),D2,“”),“,”,“”))
If you wanted to do this all in one cell, then another formula I came up with is…
=RIGHT(B2,LEN(B2)–FIND(” “,B2&” “,FIND(“,”,B2)+2)+1)
Formula Explanation
SUBSTITUTE: =SUBSTITUTE(text_to_search, search_for, replace_with, [occurrence_number]) Substitute will replace search_for with replace_with in text_to_search. Breaking down the first formula, we replace the last name with an empty character: “”. Then, we replace the first name and the comma with a “”. It’s a very simple way to get the middle name only. However, if we want to get the middle name without relying on other cells, you could just replace F2 in the first formula with the last name formula from above and D2 with the first name from above as well… however, you could make the formula much shorter by using the second formula I provided for the middle names. Breaking this down, it will find the space after the first word after the first comma and get everything to the right of it.
Combining First and Last Names Together
The final step was to combine the first and last names together. We can do this with a very simple formula. The first name is in D2 and the last name is in F2.
=D2&” “&F2
If you wanted to do this in one cell without needing any helper columns, you could just replace D2 with the first name formula above and the last name formula above. Remember that in my example, I put the raw names in B2. I have done the combining for you already and you get:
=LEFT(TRIM(RIGHT(B2,LEN(B2)-FIND(“,”,B2))),FIND(” “,TRIM(RIGHT(B2,LEN(B2)-FIND(“,”,B2&” “)))&” “)-1)&” “&LEFT(B2,FIND(“,”, B2, 1)–1)
Hope this helps you out for those of you who need help with separating first, middle, and last names!