Contextures

Excel Split First and 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.

full name in cell

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

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.

Text to Columns command

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

Text to Columns wizard step 1

  • 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

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

Text to Columns wizard step 2 finish

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

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

full name in cell

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.

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

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.

flash fill list does not update

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 formula. Using a 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.

This video shows the steps, and there are written steps below the video.

Reverse First & Last 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

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.

first and last names reversed

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

how the short formula works

Download the Sample File

Download the sample file for splitting and 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

Last updated: November 21, 2021 2:26 PM
Contextures RSS Feed