Recently, a friend asked about fixing an Excel spreadsheet where his subscriber names were in one column. The data was correct, but the problem was the cell contained the full name. He wanted separate first and last names in Excel so he could send a personalized greeting. After all, most of us would prefer to get an email saying “Hello Anne” instead of “Hello Anne Zachary.” Excel has a handy feature called Convert Text to Columns Wizard that can solve this problem.
Think Beyond Names
Although this problem 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 a recognizable pattern or Excel substring. The typical ones include tabs, semicolons, commas, hyphens, and spaces. If you go to the Data tab, you’ll see a Text to Columns item. It seems people dismiss this choice because their data is already in columns.
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” field. So, having the middle portion messed things up for him. In other words, the Text to Columns wizard wasn’t the best solution.
A better solution would be to use nested 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 a formula is that I’m keeping the initial Name field untouched. To get at the First name, I’ll use a formula to search within the Name 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.
Last Names & Substitutions
I’ll admit I thought the last part would be easier and I don’t have a perfect formula because of outliers. The one I settled on came from an ExcelChamps post.
=TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",LEN(A2))),LEN(A2)))
The problems I encountered were suffixes and surnames with spaces. Sorry, but examples like James Jones Jr. or Vincent Van Gogh made me crazy.
This may be a scenario where if you’ve got a lot of outliers you might want to entertain a commercial Excel add-in like AbleBits Split Names tool which is part of their Ultimate Suite.