Have you got a long list of people’s names in Excel and you’re not sure how to sort or filter them by surname?
The challenge occurs when the first and last name are in a single cell. You may also have middle names jammed in there.
And worse, there may be a prefix (Mrs.) or a suffix (Jr.) to make things more complicated.
This single-cell format makes it difficult to sort, filter, and analyse your data based on surnames or first names.
This tutorial walks you through several options to split the names into multiple columns.
Prefer A Video Walkthrough?
If you prefer a video tutorial, then this article’s content is covered here:
Keep reading if you prefer a an article format.
How Complex Is Your List Of Names?
The simplest list is formatted where there is one first name followed by a surname:
| Anne Young |
| Joseph Young |
| Mary Young |
A slightly more complex format has the last name first, usually followed by a comma:
| Young, Anne |
| Young, Joseph |
| Young, Mary |
If your list follows either format consistently, then I recommend that you use Excel’s built-in feature “Text to Columns” to separate your names. It just takes a few clicks.
More complex formats
The more complex formats may have:
- multiple middle names e.g. Mary Mabel Sarah Young
- suffixes e.g. Victor Young Jr.
- prefixes e.g. Dr. Walter Young
If your list has a mix of lots of different formats, then it’s more of a challenge to separate them consistently. The formulas can get quite complex, but I’ll give you working examples to copy and paste.
But let’s start with the simplest formats and work our way up.
Use “Text to Columns” To Split “FirstName LastName”
Our sample data has these names in column A:
| Anne Young |
| Joseph Young |
| Mary Young |
Where is the “Text to Columns” feature in Excel?
The “Text to Columns” feature is available from the Data tab in the top ribbon:
Have you got enough space?
The one thing to remember in advance is that you need enough space to the right of the column you are splitting.
If you think that the split will result in three separate columns, then you need two empty columns to the right of the one you’re working with.
If there is data in the third column, then it will be overwritten by the split.
Steps
Start by following these steps:
- Highlight the cells with the data you want to split.
- Go to the Data ribbon.
- Click “Text to Columns” in the “Data Tools” section.
Now you pick the correct delimiter, which is what separates the last and first name. In our case, the space as the delimiter.
Here are the results:
| Anne | Young |
| Joseph | Young |
| Mary | Young |
Use “Text to Columns” To Split “’LastName, FirstName”
What if your data is formatted with the last name first, followed by a comma and a space?
| Young, Anne |
| Young, Joseph |
| Young, Mary |
Follow the three steps in the previous section, but you change things up when choosing the delimiters to use.
With data like this, you should choose both the comma and space.
Using Excel Formulas To Split Names
You may prefer to use Excel formulas to deal with your list. You can use them with the simple formats, but they are necessary when the formats are more complex.
The next sections give you examples you can copy-paste-edit into your own spreadsheet. But read the warning section next – quotations don’t copy well from a web page into Excel.
Warning: Copy-And-Paste Issues
If the formula doesn’t work after pasting it into Excel, please check the quotation marks.
Excel requires straight quotes (” “), so you might need to replace them manually if they appear as curly quotes (“ ”) in Excel.
Formulas For “FirstName LastName”
Let’s say the name “Anne Young” is in cell A1. You want to separate the first name into cell B1 and the surname into cell C1.
This formula in cell B1 will extract the first name:
=LEFT(A1, FIND(” “, A1) – 1)
It works by looking for the space between the two words and grabbing the word to the left of the space.
This formula in cell C1 will extract the last name by grabbing the word to the right of the space:
=MID(A1, FIND(” “, A1) + 1, LEN(A1))
Formulas For “LastName, FirstName”
In this scenario, the list of names are formatted with the last name first. An example looks like this: “Young, Anne”.
The key for our formula is that there’s a comma between the last and first name. You can use the FIND() function to find the comma and then separate the text before and after it.
The LEN() function figures out how many characters (the length) are to be grabbed after the comma.
=TRIM(MID(A1, FIND(“,”, A1) + 2, LEN(A1)))
Here’s the formula for the surname which is to the left of the comma:
=LEFT(A1, FIND(“,”, A1) – 1)
Formulas For Complex Name Formats
Let’s say that the names in your spreadsheet may have one or more middle names like shown here:
| Anne Young |
| Joseph Terence Young |
| Mary Mabel Sarah Young |
Now you’ve seen the examples, let’s look at the formulas.
First and Middle Names
This formula in cell B1 will extract the first and any middle names from cell A1.
=TRIM(LEFT(A1, LEN(A1) – LEN(TRIM(RIGHT(SUBSTITUTE(TRIM(A1), ” “, REPT(” “, 100)), 100)))))
This formula works by replacing each space in the name with a long string of spaces (equal to the length of the name), then trimming everything to the left of the last space.
Basically, it extracts everything except the last word in the string, which is presumed to be the surname.
Last Name
Here is the formula to extract the last name:
=TRIM(RIGHT(SUBSTITUTE(TRIM(A1), ” “, REPT(” “, 100)), 100))
This formula uses spaces to gets everything except the last word in the cell, which is presumed to be the surname.
More Formulas For Complex Name Formats
Suppose the names in your spreadsheet look like this:
| Young, Anne |
| Young, Joseph Terence |
| Young, Mary Mabel Sarah |
First and Middle Names
This formula in cell B1 will extract the first and any middle names from cell A1.
=TRIM(MID(A1, SEARCH(“,”, A1) + 1, LEN(A1) – SEARCH(“,”, A1)))
This formula works by searching for the comma and extracting everything to the right of it.
Last Name
This formula dropped into C1 gets everything to the left of the comma, in other words – the last name.
=LEFT(A1, SEARCH(“,”, A1) – 1)
Download Our Excel Template
We have a free Excel template that contains these formulas for you to copy.
You’ll also receive our newsletter, you can unsubscribe at any time.
