Suppose you have a list of full names in Excel that you wish to split into first and last names. This task comes up a lot when working with lists of customers, applicants, class attendees, etc.
There are basically 2 ways of separating first and last names in Microsoft Excel. Method 1 involves using functions and formulas while Method 2 involves using Excel’s built-in Text-to-Column feature. Both are extremely easy to use.
Let’s take a look at each one method using row 3 in the screenshot below as an example.
Method 1: Separate using functions
The first method makes use of functions. By nesting two functions together we can find the position of the space (or some other character) between the first and last names, and then use that to extract specific parts of the full name.
Find the position of the space
We can find the position of the space in the full name using the SEARCH() function, which returns the position of a text string or character within another text string. It takes the following inputs:
- find_text is the character to look for. In our case, this will be ” ” (the space between the first and last names).
- within_text is the text string within which to search for find_text. We’ll be inserting a cell reference to B3.
- [start_num] is an optional parameter to specify what character in the string Excel should start searching from (working from left to right). In our case, we’ll just leave it out entirely to have Excel use the default value of 1.
So our SEARCH() function will look like this:
=SEARCH(” “, B3)
Extract the first name from the full name
Next, we can extract the first name using the LEFT() function, which returns a specified number of characters from the start of a text string. It takes as input the following:
- text is the text string from which to extract. We’ll use a cell reference to B3.
- [num_chars] is an optional parameter to specify the amount of characters to extract. Since we’ve found the position of the space (and consequently the end of the first name) using the SEARCH() function above, we can enter that entire function here.
So the combined formula to extract the first name will be:
=LEFT(B3, SEARCH(” “, B3))
Extract the last name from the full name
To do this we’ll use the RIGHT() function, which returns a specified number of characters from the end of a text string. To use it, we need to know the length of the last name (so the we can tell Excel how many characters to extract).
You would agree that the length of the last name would be equal to the length of the full name minus the length of the first name. We have already found the length of the first name above, and we can easily find the length of the full name by using the LEN() function, which determines the length of a text string.
length 0f last name = length of full name – length of first name
length of last name = LEN(B3) – SEARCH(” “, B3)
So armed with this expression, we can now extract the last name:
=RIGHT(B3, LEN(B3) – SEARCH(” “, B3))
Copy the function down to all rows
Once you have the function down for the first row, you can copy it down to the other cells as well. Select the formula you’ve just entered and double click the block icon in the bottom right hand corner of the cell.
Method 2 – Separate using Text to Column
The second method involves using a built-in tool to divide lines of text into columns based on the characters between the words. Here are the steps to follow:
- Select all the cells containing the full names with your mouse.
- On the Data tab, click Text to Column. This will present you with a dialogue box.
- In step 1 of the dialogue box, you can tell Excel how your data is organized. You can either choose Delimited if there is some known delimiter (separator) between every name and surname. If you know that all names and surname would be of the same length, you could choose Fixed width as well. Since that’s rarely the case, we’ll select Delimited in this example.Click Next.
- In step 2 of the dialogue window, you can specify the delimiter (character separating name and surname). There’s a host of options to choose from, including tab, semicolon, comma, space, or some other character you can type in manually.In this case, the delimiter is a space, so check the Space box. As you do so, you can immediately see new columns being defined in the Data preview section.We’ll leave the Treat consecutive delimiters as one textbox checked, since this will allow Excel to correctly spot cases where two and three spaces are used between the name and surname, and treat them as one delimiter. Click Next.
- In step 3 of the dialogue window, you can specify the destination for the new first and last names, as well as the data type of those cells. We’ll select General for the data type (although Text would also suffice), and insert D3 as the destination.
These are the two methods of separating fist and last names in Microsoft Excel. Which one is better?
Both have pros and cons. If you don’t like getting your hands dirty with formulas, then Method 2 is for you. But Method 1 does have its advantages. Since it’s written as a formula, you can adapt it to perform many other tasks as well (such as making sure the first letter of each name is capitalized, with the PROPER function for instance).
But in the end, both perform the same duty. You choose which one works best for you.