Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, and 2016. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Separating Names into Individual Columns.

Separating Names into Individual Columns

by Allen Wyatt
(last updated September 9, 2017)

3

Lance has a set of names in a workbook. The names are all in column A, and some have first and last name, while others use first, middle, and last names. He needs to separate the names into individual columns, but the Text to Columns wizard doesn't provide satisfactory results. It does the separation OK, but the two-vs.-three names issue means that Lance need to do a lot of manual massaging of the data once it is split up.

The solution to the problem is to not rely on the Text to Columns wizard, but instead use a number of formulas to get the names into columns. The results you achieve still depend, in large part, on the condition of the data you are parsing. If your data is in the format "first middle last" (with the middle name being optional), then you can use the following formula to pull out the first name:

=LEFT(A1,SEARCH(" ",A1,1)-1)

This formula checks for the first space in the name, and then assumes that everything before that space is the first name. The next formula is used to determine if there is a middle name, and if there is, display it:

=IF(ISERROR(SEARCH(" ", RIGHT(A1, LEN(A1) -
SEARCH(" ", A1, 1)), 1)) = TRUE, "", LEFT(
RIGHT(A1,LEN(A1) - SEARCH(" ", A1, 1)),
SEARCH(" ", RIGHT(A1, LEN(A1) - SEARCH(" ",
A1, 1)), 1) - 1))

Even though the formula shows as 5 lines here, it is really a single formula and should be entered as such. The formula checks for the existence of a second space in the name. If an error is generated (there is no second space) then the formula returns nothing; there is no middle name. If a second space is detected (there is no error generated), then the formula returns everything from after the first space up through the second space.

The next formula is used to extract the last name:

=RIGHT(A1,LEN(A1)-LEN(B1)-LEN(C1)-IF(C1="",1,2))

This formula relies on the results that were returned for both the first and middle names. It returns everything left in the original name after accounting for the length of the first name (assumed in cell B1) and the middle name (assumed in C1). (See Figure 1.)

Figure 1. Separating names into columns.

Again, these formulas work if the names in column A follow some pretty strict guidelines. The names must be in the "first last" or "first middle last" format. If there are qualifiers in the name such as Ms., Dr., Jr., or III, then you won't get satisfactory results. In addition, if the last name contains a space (as in "John van Kamp" or "Mary Anne St. Marie") or if the name contains no spaces at all, then you also won't get satisfactory results.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (12383) applies to Microsoft Excel 2007, 2010, 2013, and 2016. You can find a version of this tip for the older menu interface of Excel here: Separating Names into Individual Columns.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Random Resetting of the Standard Toolbar

Excel allows you to easily customize what appears on its various toolbars. If you make customizations to the Standard toolbar ...

Discover More

Subtotals Option Grayed Out

The Subtotals option on the Data menu is normally available for adding or removing subtotals to data tables. If the option is ...

Discover More

Removing Shortcuts for Macros

Word apparently has a bug in it that makes it very difficult to remove a shortcut key previously associated with a macro. ...

Discover More

Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!

More ExcelTips (ribbon)

Using a Numeric Portion of a Cell in a Formula

If you have a mixture of numbers and letters in a cell, you may be looking for a way to access and use the numeric portion of ...

Discover More

Summing Only Positive Values

If you have a series of values and you want to get a total of just the values that meet a specific criteria, then you need to ...

Discover More

Finding the Address of the Lowest Value in a Range

Uncovering the lowest value in a range is relatively easy; you can just use the MIN worksheet function. Discovering the ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 8 - 3?

2017-09-11 07:21:36

Syed Sabihur Rahman

Actually, Text to Columns worked just fine for me when I tried to solve this problem. The First and Last names actually showed up in adjacent columns unlike the blank column which shows up in this case.

In cases where there are no Middle names, a simple BLANK search for the third column will suffice to know whether the word displayed in the second column is the Middle Name or the Last name.


2017-09-11 05:04:00

David Robinson

Me, I think I'd use helper columns to store the locations of each space character found, then use these in conjunction with MID. I think this'd be easier if there are names with two middle names, for example.

For surnames with "van", "von", "de", "jr", "III" etc., you could do a series of nested substitutions before finding the last space. These substitutions would remove the space between the "van" etc. and the rest of the surname, so you'd replace " van " (spaces both sides) with " van" (no space after) for each surname prefix, and you'd replace " jr" (space before) with "jr" (no space before) for each surname suffix. Do this and then find the last space, and then use MID to use this location as the start point, but use MID on the original string so you return the surname with all the spaces still there. Use MID, not RIGHT, because MID will count from the left, and therefore those space characters you removed won't mess anything up because they were from after that position.

Incidentally, to be on the safe side (because it's conceivable that the letters "jr" might be the start of someone's middle name or surname), it'd be safer to append a termination character (e.g. \, or even just a space) to the end of the name being tested, and then you can look for " jr\".


2017-09-10 17:26:43

Alec Whatmough

I too had Lance's issue when using Excel to manipulate employee data for nearly 5000 people. Eventually, I woke up to using formulae to extract first and last names instead of TTC, as per this tip.
Of course, there were issues with surnames like "Van Gogh", Van de Berg", "Le Roux" etc.
My solution, rather than trying to write the nested IF formula from hell, was to use conditional formatting to show any cells that contained " Van ", " Le ", " St" etc, then fixed those manually. This may not be so effective in parts of Europe, though, so I'd love to see a more fool-proof method!


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.