Formulas > Text > Split Names

Excel Split or Reverse First Last Names

Quickly split first and last names into separate cells, with Excel tricks or formulas. See how to reverse order of names, from "Smith, Mary" to "Mary Smith", by using the MID function.

Split Comma-Separated Names

In column A, there is a list of names, with the last name, followed by a command, and then the first name(s). A quick way to separate the names, and split them into two separate columns, is to use the Text to Columns feature.

NOTE: This list is in a named Excel Table. You can follow the same steps for a regular list on a worksheet.

Remove Space After Comma

Before splitting the names into separate cells, follow these steps, to remove the space after the comma. Otherwise, a space will appear before the first name, when it is sent to its own column. It would take extra steps to clean it up in that cell.

Before you begin, make a backup copy of the worksheet, or the workbook, just to be safe.

• Select the cells that contain the names -- A2:A6 in this example
• To open the Replace window, press Ctrl + H
• In the Find What box, type a comma, followed by a space character
• In the Replace With box, type a comma
• Click Replace All, then click OK, in the confirmation message
• Click the Close button.

Split the Names

Next, we'll use the Text to Columns feature to split the cell contents into separate columns, based on the position of the comma..

• Select the cells that contain the names -- A2:A6 in this example
• On the Excel Ribbon, click the Data tab
• In the Data Tools group, clck the Text to Columns command.

• In the Convert Text to Columns Wizard, Step 1 of 3, select the Delimited option, then click Next

• In the Convert Text to Columns Wizard, Step 2 of 3, add a check mark to the Comma delimiter, and remove any other check marks

In the Data preview window, you can see where the names will split. Because we removed the space after the comma, there is no space character before the first names.

If we hadn't removed the space after the comma, you would see a space character before each of the first names.

• For this data, there is no need to go to Step 3, where you can set the data format for each column, or mark columns that should not be imported, so you can click the Finish button in Step 2.

On the worksheet, the last names are in column A, and the first names have moved to column B.. Because the list is a formatted Excel table, a new column was automatically added to the table, with the default heading, "Column1".

• To complete the name split, change the column headings, to Last Name and First Name..

Reverse First & Last Names - Flash Fill

In column A, there is a list of names, with the last name, followed by a command, and then the first name(s). In Excel 2013, and later versions, you can use the Flash Fill feature, to quickly reverse the order of the names, to show the first name, then a space, and then the last name.

To use Flash Fill, type a couple of names to get started, or use the Flash Fill shortcut.

NOTE: This is a static fix -- the reversed names are not linked to the original names, and will not update if the full name is changed. For a dynamic fix, use the Reverse Names formula, in the next section

Type the First Name

• Type a heading -- FirstLast -- in cell B1
• In cell B2, type the reversed name from cell A2 -- Mary Smith -- then press Enter

• In cell B3, begin typing the reversed name from cell A3 -- Lou Jones

As you type, the Flash Fill feature detects a pattern, and fills in the remaining cells, based on that pattern. The list is in a light font color.

• To accept the Flash Fill items, press the Enter key.

The suggested items are entered in the remaining cells, and the font changes to a dark color.

Flash Fill Shortcut

Thanks to Karen Roem, who sent these steps for using the Flash Fill Shortcut - Ctrl + E.

TIP: The same shortcut works to Flash Fill other changes, such as changing text case to Proper, UPPER or lower.

• In cell B2, type the reversed name from cell A2 -- Mary Smith -- then press Enter
  • 
• In cell C2, press the keyboard shortcut for Flash Fill - Ctrl + E
• The remaining names will fill down in column B
  • 

Flash Fill - Static Fix

REMEMBER: This is a static fix -- the reversed names are not linked to the original names, and will not update if the full name is changed, or if new names are added. For a dynamic fix, use the Reverse Names formula, in the next section.

Video: Flash Fill Shortcut

In this short video, Sarah shows how to separate data from one cell, into multiple columns, using the Flash Fill Shortcut - Ctrl+E

Reverse First & Last Names - Formula

You can also reverse first and last names with a Microsoft Excel formula.

• In column A, there is a list of names, with the last name, followed by a comma, and then the first name(s).
• In column B, you'll use a formula to reverse order of the names, to show the first name, then a space, and then the last name.

Using one of the following formulas creates a dynamic fix, instead of a one-time solution. The formula results will update immediately, if the full name in column A is changed.

Use a Short Formula or Long Formula

There are two formulas shown below, to reverse the full names shown in the screen shot above:

• Short formula - reverses names by searching for a space. This is the more efficient solution.
• Long formula - reverses names by searching for a comma. This is an older formula, and you might see it in Excel workbooks that you inherited, and you want to understand how it works.

Reverse First & Last Names - Short Formula

This video shows the steps to create a short formula, and there are written steps below the video.

NOTE: There is also a longer formula, in the next section.

Reverse Names - Short Formula

In column A, there is a list of names, with the last name, followed by a comma, and then the first name(s).

Follow these steps to create a formula to reverse first and last names:

• In cell B1, type a heading - Name FirstLast
• Press Enter, and the named table will expand to include column B
• In cell B2, type this formula:
• =MID(A2&" "&A2,FIND(", ",A2)+2,LEN(A2)-1)
  • Note: Cell references (A2) were used, for simplicity. To use table references instead, replace each A2 with [@[Full Name]]
• The formula automatically fills down to the last row in the table.

How the Formula Works

The formula uses the MID function, which has 3 arguments (text, start_num, num_char)

Text: The & operators join two copies of the full name, with a space between them

Smith, Mary Smith, Mary

Start_Num: FIND function returns the position number of the comma-space, and 2 is added to that number. In this example, the comma is the 6th character, so the Start_Num is 8 (6+2)

Num_Char: LEN function returns the number of characters in the full name. The comma will not be in the reversed name, so 1 is subtracted from the length. In this example, the length is 11, so the Num_Char is 10 (11-1)

Result - In cell B2, the result is Mary Smith.

It starts at the 8th character of "Smith, Mary Smith, Mary", and returns 10 characters.

Reverse Names - Long Formula

Another way to reverse names from "Smith, Mary" to "Mary Smith", is by using a long formula, with the RIGHT, LEN, SEARCH and LEFT functions. This video shows how to build the long formula, and there are written steps, and the full video transcript, below the video.

NOTE: I prefer to use the shorter formula now, shown in the previous section. I left this formula here, in case you saw it used somewhere, and want to understand how the longer formula works.

Reverse Names - Long Formula

This long formula reverses names by searching for the comma, and it can be used for last names that are one word, or multiple words with a space character.

This list in this example is a named Excel Table, so the formulas will have field references, such as [@[Full Name]]. For a regular list on a worksheet, use cell references, such as A2, instead of field references.

=RIGHT(A2,LEN(A2) - (SEARCH(",",A2,1) + 1)) & " " & LEFT(A2,SEARCH(",",A2,1) -1)

Find the Comma

First, we'll use the SEARCH function, to locate the comma. That will tell us where to split the names.

• In cell B1, type a heading for the column: Name FirstLast
• In cell B2, type the first part of the formula, which will find the comma:

=SEARCH(",",[@[Full Name]],1)

• Press Enter, to complete the formula

Because the list is in a named Excel table, the formula will automatically fill down to the last row in the table.

The formula result shows the position of the comma in each name, with 6 as the result in cell B2

Get the Last Name

The last name is to the left of the comma, so we'll use the LEFT function to extract that text from the full name.

• In cell B2, add "-1" at the end of the formula, because we don't want to include the comma as part of the last name

=SEARCH(",",[@[Full Name]],1) -1

That number can be used as the length, for the LEFT function.

• In cell B2, click after the = in the formula, and type LEFT(

=LEFT( SEARCH(",",[@[Full Name]],1) -1

• The first argument of the LEFT function is text, so click on cell A2 -- that is the cell with the text that you want to pull the string from. The field reference will be added to the formula.

=LEFT([@[Full Name]],SEARCH(",",[@[Full Name]],1) -1

• At the end of the formula, type a closing bracket ), and press Enter.

=LEFT([@[Full Name]],SEARCH(",",[@[Full Name]],1) -1)

The revised formula will automatically fill down, and show the last name from each full name.

Add a Space Character

There should be a space character between the first and last names, so we'll add one, in front of the last name.

• In cell B2, click after the = and type a space character, inside double quotes, followed by an ampersand, then press Enter

=" " & LEFT([@[Full Name]],SEARCH(",",[@[Full Name]],1) -1)

Now you can see a space character before each of the last names.

Start the First Name

The first name is at the right of the full name, so we'll use the RIGHT function to pull the text. Later, we will calculate the exact number of characters in the first name, but we'll temporarily use a set number of characters -- 4.

• In cell B2, click after the = in the formula, and type RIGHT(

=RIGHT( " " & LEFT([@[Full Name]],SEARCH(",",[@[Full Name]],1) -1)

• The first argument of the RIGHT function is text, so click on cell A2 -- that is the cell with the text that you want to pull the string from. The field reference will be added to the formula.

=RIGHT([@[Full Name]] " " & LEFT([@[Full Name]],SEARCH(",",[@[Full Name]],1) -1)

• Next, type a comma, then 4 as the number of charcters to pull

=RIGHT([@[Full Name]],4 " " & LEFT([@[Full Name]],SEARCH(",",[@[Full Name]],1) -1)

• Type a closing bracket for the RIGHT function, and type an ampersand, to join the first name to the space character and the last name, and press Enter

=RIGHT([@[Full Name]],4) & " " & LEFT([@[Full Name]],SEARCH(",",[@[Full Name]],1) -1)

Now you can see a first name before each of the last names. However, some of the names are too short (cell B6) and some are too long (cells B3 and B4)

Find Length of First Name

To calculate the length of the first name, we can

• calculate the length of the full name
• find the comma position
• add one to that position, for the space character
• subtract the space character position from the length of the full name

Here are the pieces for that part of the formula.

• Use the LEN function to get the length of the full name: LEN([@[Full Name]])
• We previously calculated where comma is located. SEARCH(",",[@[Full Name]],1)
• For the space character position, add 1 to the comma position, and wrap with brackets, so this calculation is done before the amount is subtracted from the full length: (SEARCH(",",[@[Full Name]],1) + 1)

Subtract the space character position from the length of the full name, to get the length of the first name:

LEN([@[Full Name]]) - (SEARCH(",",[@[Full Name]],1) + 1)

Complete the Formula

In the existing RIGHT formula, we'll replace the "4" placeholder

=RIGHT([@[Full Name]],4)

with the length calculation formula

=RIGHT([@[Full Name]],LEN([@[Full Name]]) - (SEARCH(",",[@[Full Name]],1) + 1))

• Press Enter, to complete the formula

The Completed Formula

Here is the completed formula, if the list is in a named Excel table -- first name & space & last name:

=RIGHT([@[Full Name]],LEN([@[Full Name]]) - (SEARCH(",",[@[Full Name]],1) + 1))
& " "
& LEFT([@[Full Name]],SEARCH(",",[@[Full Name]],1) -1)

If your list is not in a named Excel table, your completed formula will have cell references, instead of field references.

=RIGHT(A2,LEN(A2) - (SEARCH(",",A2,1) + 1))
& " "
& LEFT(A2,SEARCH(",",A2,1) -1)

In the completed formula the first name is now the correct length, and is followed by a space character, and the last name.

Video Transcript - Long Formula

In this workbook, there's a list of names in a table. The names show the last name, then a comma, and the first name. We're going to use a formula to change the names, so the first name is first, then a space, and then the last name.

To do that, we'll build a formula in column B and we'll take a quick look at it now. It's going to go to the right side of the cell and get the first name and then put a space, then go to the left side of the cell and get the last name.

Long Formula

We're going to break this down into steps to see how this long formula works. The first thing we have to do is find where the comma is in the name. Here's Smith, Mary, and there's a space character here after the comma.

If we counted this, we could see that the comma is the sixth character in that cell and that comma is going to tell us where the last name ends and then we'll be able to figure out where the first name begins.

Find the Comma

So our first step will be to use a formula to find that comma. In this cell, I'm going to use a SEARCH function that will let me look for a specific character in a string of text.

• In this cell, I'm going to type =SEARCH, then an open bracket.
• The first argument is find text, so we'll have to specify what we're looking for and put that inside double quotation marks.
• We're looking for a comma, so I'll do double quote comma, double quote, and then I'll put a comma.
• The next argument is within text, we're looking for that comma in this text.
• I'll click on the full name and then a comma. And the place to start looking is at character one.
• Put a closing bracket and press Enter.

And because we're in a table here, that formula fills down automatically.

If we look at the formula, it's using the field names as references. So instead of A2, we're going to see the full name field here.

If you're not using a table, you would just click on the cell and it would show A2. So that tells us where the comma is.

Get the Last Name End

Our next step will be to use that number to get the last name. We know that the last name ends just before the comma.

• We can put a minus one at the end of the formula that we have so far and that will give us the last character in the last name.
• Click in the formula bar and type minus one and press enter.

Now, based on that comma location and subtracting one from it, we know the length of the last name.

Get the Last Name

From this we can use the LEFT function to pull that number of characters from the left of the cell.

• So going back to cell B2 I'm going to click just after the equal sign and type LEFT open bracket.
• We have tell it where the text is that we want to get the left characters from, so I'll click on the full name cell again and then a comma.
• And we've already figured out the number of characters with our SEARCH formula here. So I'll click at the very end and add a closing bracket and press enter.

And now we have the last name for each of these full names.

Add a Space

The next thing we're going to add is a space. Just before this last name, we're going to get the first name and we don't want it right beside the last name, we want a space between them.

• So back to cell B2 and click just after the equal sign.
• To create a space character, I'm going to type, double quote space, double quote
• To join things in a formula, we use the ampersand character, so I'll type one here.

So now we'll have the space character joined with the last name, when I press Enter. So there, we can see a little space at the beginning of each of these cells.

Start the First Name

Now next we have to figure out how to get the first name. We're going to be getting characters from the right side of the cell, but we're not sure how many.

• So to start, I'm going back to cell B2, and I'm going to click just after the equal sign
• Because we want to pull characters from the right side of this text string, we're going to use the RIGHT function.
• Type RIGHT and then an open bracket.

Number of Characters

For the RIGHT function, again, just like the LEFT, we have to tell it where the text is that we want to get the text from the right, so we'll click on the full name cell and then a comma

And we have to know how many characters we want to pull to get that first name. So Mary has 4 and 3 and 3 and 4 and then a longer name here.

We're going to just for now put in a place holder number.

• We're going to figure out the exact number later, but I'm just going to type 4 which is the number of characters in Mary's name.
• Then a closing bracket.
• We want to join that first name with the space and the last name.
• We'll type another ampersand and press enter.

Mary's name looks correct. There's a space before the next two names. And then Bill is okay because his name is four characters and Anne Marie's name just got chopped off.

Full Name Length

So our next task will be to figure out how many characters each name is. We don't want to have to type that in our formula. We'll look at this breakdown of what's in that cell. So it's Mary Smith and we've already figured out how to get that comma position.

And in this case, the first name ends, not at the comma, but there's a space after and then the name begins. We're going to have to figure out how many characters are in that full name, including the comma and space. We know how to get that comma. And then we're going to add one to that.

Then we'll take the length of the full name, subtract the position of the space character, and that will give us the number of characters in the first name.

• So I'm going to replace this 4, just highlight it and I'm going to type LEN, l, e, n,
• That will give us the length of a text string, open bracket.
• We want to get the length of the full names. We'll click on that cell, close the bracket and press enter.

So that's this full name length that we've calculated.

Comma Position

Now to get the position of the comma. We've done that previously. We'll take a look and just copy that part of our formula.

• In our formula we used SEARCH and I'm going to select all of that. That was the bit of formula we use to find the position of the comma.
• I've highlighted it and I'll press Ctrl C to copy it.
• I'm going to click just after the calculation for LEN. It's going to be the length minus and we're going to take our comma position plus one.
• So open bracket, paste in the copied formula for SEARCH.
• To that, we're going to add one. So plus one, close the bracket. Now our first name is the full length minus the comma position plus one.
• Press Enter.

Now we can see Mary, Lou, Sam, Bill, and Ann Marie. First names all appear correctly.

For this formula, to make it easier to read, I'm going to put in some line breaks

• I'm going to find the spot where we start the space character.
• I've clicked just in front of the ampersand here, press the Alt key and Enter
• Then do the same for the last name.
• Click here, Alt enter.
• So now we can see the first name from the right, a space character, and the last name from the left

So press Enter, and there's our names reversed from last and first, now showing first and last.

Download the Sample File

Split or Reverse Names: Download the sample file for splitting and reversing full names. This workbook has the names for testing Text to Columns, Flash Fill, and the short formula to reverse names. The zipped file is in xlsx format, and does not contain macros.

Reverse Names - Long Formula: Download the sample file with long formula for reversing full names. The zipped file is in xlsx format, and does not contain macros.

More Tutorials

Fill Blank Cells

Convert Text to Numbers

Increase Numbers With Paste Special

Data Entry Tips