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

Changing Input Conventions

Different cultures have different conventions for displaying numbers and for parameters in Excel's worksheet functions. ...

Discover More

Enforcing a Do-Not-Use Word List

Got a list of words you don't want to appear in your documents? There are a number of ways that you can make sure they don't, ...

Discover More

Aligning Decimal Numbers in Tables

Need to align numbers around their decimal point within a table? It's easy to do by using the three simple steps provided in ...

Discover More

Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!

More ExcelTips (ribbon)

Listing Combinations

You can easily use the COMBIN worksheet function to determine the number of combinations that can be made from a given number ...

Discover More

Returning the Minimum of Integers of a Range

If you have a range of numbers that contain both integers and decimal numbers, you may have a need to determine the minimum ...

Discover More

Adding a Statement Showing an Automatic Row Count

If you want to add a dynamic statement to a worksheet that indicates how many rows are in a data table, you might be at a ...

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 two more than 4?

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.