Recently, a friend asked about fixing an Excel worksheet where subscriberwere in one column. He wanted to separate first and last names in Excel so he could send personalized greetings. In this tutorial, I’ll first show how to use a feature called Convert Text to Columns Wizard. Then we’ll use some Excel formulas and Excel Flash Fill to handle outliers.
Think Beyond Names
Although this tutorial is about a Name field, the same issue can happen elsewhere. It’s easy to create spreadsheets without thinking about how the data will be used. I’ve been guilty of this myself. Here are a few examples I’ve seen where too much data was in one column and needed to be split up or parsed.
- Phone numbers
Having too much data in one field can present problems. In the case of my friend, it was personalization. Other times, too much data can present sorting and filtering issues.
The key to solving this problem is to parse or split the data based on pattern matching or Excel substrings. The typical separators include tabs, semicolons, commas, hyphens, and spaces. For example, if you go to the Data tab, you’ll see a Text to Columns item. Unfortunately, it seems people dismiss this choice because their data is already in columns.
Before we begin, there may be an easy way to parse your cells using Excel’s flash fill feature. It offers easy parsing where you have consistent fields. This solution doesn’t require formulas, but pattern matching. And later in 2022, Microsoft will be releasing more text functions for Microsoft 365 users including TEXTBEFORE, TEXTAFTER, and TEXTSPLIT.
How to Separate First and Last Names in Excel
- Right-click the column header to the right of the names you wish to split and select Insert. For example, if your names are in Column A, click Column B. This new blank column is where we will put the Last Name.
- Click the column header of the column you wish to split. In my example, the names are in Column A.
- From the Data menu, select Text to Columns. A data wizard appears.
- Choose the Delimited radio button in the Original data type section.
- Click Next.
- In the Delimiters section, select the item that separates your data. In my example, a space separates the First and Last name.
- Look at the Data preview section. If the data looks correct, click Next.
- Click the first column in the Data preview section and select the Column data format type. I’ve kept the default Destination range.
- Click Finish.
- Label your new column.
Outlier Middle Names & Initials
Not too long after I solved my friend’s problem, he called to tell me my solution no longer worked. After asking several questions, a small percentage of people put in their middle name or middle initial. I reminded him the earlier solution gave him his needed “first name.” He then told me he was also using the “last name” or surname field. So, having the middle part messed things up for him and the Text to Columns wizard wasn’t the best solution.
A better solution would be to use Excel formulas that work a bit like the wizard in that we’re searching the cell for spaces. The goal was to get something like the example below.
Searching LEFT for the First Name
One difference in using an Excel formula is that I’m keeping the initial Name (President) field untouched. So, to get at the First name, I’ll use a formula to search within the President cell contents.
- Insert a new column called First to the right of your Name column. In my example, this is column B.
- In cell B2 enter
=LEFT(A2,SEARCH(" ", A2) -1)).
- Grab the sizing handle in the lower-right corner and drag it down to copy the formula to other cells.
In this example, I’m searching for the first instance of a space in cell A2. Once it’s found, I move left and grab the contents. This works well unless someone with no spaces, like Cher, signs up for my friend’s newsletter.
Handling the Middle Name
I’ll admit I thought this part would be easier. Unfortunately, I don’t have a perfect formula because of outliers. Yes, even US Presidents can be outliers. That’s a fancy way of me saying it’s not perfect and didn’t handle Martin Van Buren. The formula parsed the name, but made his last name Buren. So I’m putting you on notice; you need to review your results.
You’ll also notice that I’ve used IFERROR in the formula. While it’s not necessary, I just didn’t like seeing all the #VALUE cell entries for people that didn’t have a middle name. So, by using IFERROR , I can substitute a space ” ” instead.
- Insert a new column called Middle. In my example, this will be column C.
- In cell C2 enter
=IFERROR(MID(A2,SEARCH(" ",A2,1)+1,SEARCH(" ",A2,SEARCH(" ",A2,1)+1)-SEARCH(" ",A2,1)), " ")
- Grab the sizing handle in the lower right corner of cell C2 and drag it down to copy the formula.
Parsing the Last Name
Usually, last names can also be handled by a formula. In this case, I used the formula mentioned by ExcelChamps.
- Insert a new column called Last or Surname.
- Type the formula
=TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",LEN(A2))),LEN(A2)))
- Grab the sizing handle in the lower right corner of cell D2 and drag it down to copy the formula.
Separate Names Using Flash Fill
Another way to parse names is to use the Excel feature called Flash Fill. It’s best used with the First name and Last name columns. The feature doesn’t rely on formulas or the Text to Columns widget. It does rely on pattern recognition, but you don’t have to define the pattern.
The idea behind Flash Fill is that you train Excel with a couple of example entries. Then, based on your entries, Excel will try to fill in the remaining entries. In some cases, you don’t even need to press the Flash Fill item on the Data menu. It reminds me a little bit of Excel custom lists, except you don’t have to define them.
As you’ve probably guessed, it will get tripped up on suffixes. Sorry, Jr.
Your list may still have oddball cases where you might want to use a commercial Microsoft Excel add-in like the AbleBits Split Names tool, which is part of their Ultimate Suite if you’ve got a lot of outliers. I’m unsure if it can handle surnames like Van Buren or Van Gogh, but it can help you with suffixes and prefixes.
Example Worksheet with Formulas
One of the things that I realized is that as careful as I think I am in entering formulas, mistakes still happen. For example, in the worksheet referenced below, I was getting an error because one of my fields had a trailing space. I’ve also been guilty of dropping the closing ) on some formulas. The good news is the Excel sample file of US Presidents has the parsed names and formulas.