Home > Formulas > Text > Split Names

Split First and Last Names in Excel

Debra Dalgleish - Contextures

Quickly reverse first and last names in Excel. Or, split first and last names into separate cells. Use built-in Excel tools, or a simple formula, with the Excel MID function.

type first name and last name

1. Reverse Names - Formula

You can reverse first and last names with an Excel formula.

  • In the screen shot below, 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 the order of the names, to show the first name, then a space, and then the last name.

Using the following formula 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.

full name in cell

1.1) Video: Reverse Names - Formula

This video shows the steps to reverse first and last names with an Excel formula, and there are written steps below the video.

1.2) Reverse Names - Formula

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

full name in cell

To reverse the names, I added a heading in cell B1, then entered this MID function formula in cell B2

  • =MID(A2&" "&A2,FIND(", ",A2)+2,LEN(A2)-1)

The formula is in a named Excel table, so it filled down the column, automatically.

In column B, each name is reversed, and the comma is removed.

  • Note: In the section below, I give an explanation of how that MID formula works.

first and last names reversed

1.3) How the Formula Works 💡

  • Note: In this section, I show details on how the reverse names formula works. It's helpful information, but not essential, so you can skip this section, if you're in a hurry!
1.3.0 Reverse Names Formula

Here's the completed reverse names formula, with the MID function's 3 arguments colour coded:

  • =MID(A2&" "&A2, FIND(", ",A2)+2, LEN(A2)-1)

The MID function returns text from a text string, and has the following 3 arguments in its syntax:

  1. text -- a text string - in the example, based on the full name in cell A2
  2. start_num -- the character number in the text string, where the text extraction should start
  3. num_char -- the number of characters to extract from the text string
1.3.1 Text Argument

For the text argument, this formula needs two copies of the name in cell A2, with a space character between them.

  • A2 & " " & A2

With "Smith, Mary" in cell A2, this is the text string that's created:

  • Smith, Mary Smith, Mary

In the centre of that text string, the person's name is exactly the result that we want:

  • Smith, Mary Smith, Mary

Tip: That simple text-combining trick saves us from creating a long, complicated formula!

Next, the MID formula will tell Excel how to extract that middle section, based on where to start, and how many characters..

1.3.2. Start_Num Argument - Where to Start

In this example, the text to extract starts at character number 8 -- the first letter in the name "Mary".

  • Smith, Mary Smith, Mary

To automatically calculate that starting number, use the FIND function.

  • First, look for a comma-space (", ") in the text, because that precedes each first name.
    • FIND(", ",A2)
  • Next, add 2 to that position number, to go past the comma-space in the text
    • FIND(", ",A2) +2

Start Num Result

In this example, the comma-space starts at character number 6.

The Start_Num is 8 -- (6+2)

  • Smith, Mary Smith, Mary
1.3.3. Num_Char Argument - How Many Charcters

Finally, the MID function needs the number of characters to extract.

  • First, the LEN function returns the total number of characters in cell A2 (Smith, Mary).
    • LEN(A2)
  • Next, 1 is subtracted from that length number, because the comma won't be used in the reversed name
    • LEN(A2) -1

Num_Char Result

In this example, the length in A2 is 11

The Num_Char is 10 (11-1)

  • Smith, Mary Smith, Mary
1.3.2 Formula Result

In the screen shot below, in cell B2, the result is Mary Smith.

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

how the reverse names formula works with MID, FIND and LEN

2. Reverse Names - Flash Fill

In column A, there is a list of names, with the last name, followed by a comma-space, 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 automatically, if the full name is changed. For a dynamic fix, use the Reverse Names formula, in the previous section

full name in cell

2.1) 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

type first name and last name

  • 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.

flash fill completes the list

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

flash fill completes the list

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

2.2) 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
    • type first name and last name
  • In cell C2, press the keyboard shortcut for Flash Fill - Ctrl + E
  • The remaining names will fill down in column B
    • type first name and last name

2.3) Flash Fill - Static Fix

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

flash fill list does not update

3. Split Names - Flash Fill

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

4. Split Names - TTC (Text to Columns)

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

A quick way to separate the names, and split names into two separate columns, is to use Excel's Text to Columns (TTC) feature.

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

full name in cell

3.1) 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.

Text to Columns wizard step 2

3.2) 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, click the Text to Columns command.

Text to Columns command

3.2a) Step 1 of 3

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

Text to Columns wizard step 1

3.2b) Step 2 of 3 - Delimiters

  • 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

Text to Columns wizard step 2

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.

Text to Columns wizard step 2 preview

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

Text to Columns wizard step 2 preview space

3.2c) Step 2 of 3 - Finish or Next

After setting the delimiters in Step 2, you can either:

  • click Finish
  • or click Next, to go to Step 3 of 3.

Finish Button

For this example, we want the split names to overwrite the existing list of full names. There is no need to go to Step 3, which has more settings.

  • Click the Finish button, to split the names, and to close the Convert Text to Columns Wizard.

Text to Columns wizard step 2 finish

Next Button

For other lists, you might NOT want the split data to overwrite the existing list. Or, you might want to apply other optional settings from Step 3.

  • To continue to Step 3, click the Next button.

3.2d) Step 3 of 3 - More Settings

In Step 3, you can apply more settings, or just review the default settings.

There are notes about each setting below the screen shot.

Text to Columns wizard step 3 settings

Column Data Format

In the Data Preview window, the column headers show the data type that was assigned to the column by default. You can leave those data types as they are, or follow these steps to change a format.

  • In the Data Preview window, click on a column to select it.
  • In the list of data formats, click on the data type you want to apply to the selected column

Destination

By default, the destination is set as the first cell in the selected range on the Excel spreadsheet.

With that destination, the split data will overwrite the existing list of full data. To avoid overwriting the existing list, follow these steps, to change the starting position:

  • Click in the Destination box
  • Delete the current destination cell address
  • Click on the Excel worksheet, and select the cell where you want the top left cell of the split data to appear
    • Note: Be sure there are clear cells to the right of the destination range, if needed

Advanced Button

I rarely use this button, which opens the Advanced Text Import Settings dialog box.

If the data that you're splitting has numbers, you can adjust the settings:

  • select a different decimal separator, and thousands separator, if needed.
  • for negative numbers, you can check the box for trailing minus signs, or remove the check mark.
  • Click the OK button, to close the dialog box

Close Step 3

After you finish adjusting the settings, click the Finish button, to split the names, and to close the Convert Text to Columns Wizard.

3.3) Separated Names on Worksheet

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".

names in saparate columns

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

change column headings

Get the Excel Files ✅

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.

Get Monthly Excel Tips!

Don't miss my monthly Excel newsletter! You'll get quick tips, article links, and a bit of fun. Add your email, and click Sign Up.

Next, when you get my reply, click the Confirm button. I add this step to protect you from spam!

More Tutorials

office supply data Fill Blanks -- In Excel, fill blank cells with values from cells above, manually or programmatically. Details on the Fill Blank Cells in Excel Column page.

 

Split Address with Formulas

Convert Text to Numbers

Increase Numbers With Paste Special

Data Entry Tips

 

 

Last updated: May 23, 2026 12:02 PM