Search Contextures Sites
Custom Search

Excel - Split First and Last Names

With a column of full names, quickly split the first and last names into separate columns. Or, if names are last name, comma, first name, reverse the order, to show first name and last name.

Split Comma-Separated Names

Reverse Order of First and Last Names - Flash Fill

Reverse Order of First and Last Names - Formula

Download the Sample File

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

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.

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

Reverse Order of First and Last Names - Formula

In column A, there is a list of names, with the last name, followed by a command, and then the first name(s). In another column, you can use a formula to reverse order of the names, to show the first name, then a space, and then the last name.

NOTE: This list is in a named Excel Table, so the formulas will contain field references, such as [@[Full Name]]. You can follow the same steps for a regular list on a worksheet, but use cell references, such as A2, instead of field references.

To reverse the names, we'll use a formula in column B. We'll build the formula step-by-step, to see how it works.

NOTE: This is a dynamic fix -- the reversed names are linked to the original names, and will update if the full name is changed. For a quick fix, in Excel 2013 and later, use the Flash Fill feature, as described in the previous section

full name in cell

Video: Reverse First and Last Names

Watch this video to see how to build the formula to reverse the first and last names. The written instructions are below the video.

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

full name in cell

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.

last name in cell

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.

last name in cell with space

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)

last name in cell

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

last name in cell

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)

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

last name in cell

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.

Get All the Excel News

For regular Excel news, tips and videos, please sign up for the Contextures Excel newsletter. Your email address will never be shared with anyone else.

Search

Search Contextures Sites

 

More Data Entry Tutorials

1. Data Entry Tips
2. Fill Blank Cells
3. Convert Text to Numbers
4. Increase Numbers With Paste Special

 

More Data Entry Links

Fill Blank Cells

Convert Text to Numbers

Increase Numbers With Paste Special

Data Entry Tips

 

 

 

 

 

 

30 Excel Functions in 30 Days

 

 

 

 

 

Privacy Policy

 

Contextures Inc., Copyright 2016
All rights reserved.

 

Last updated: May 25, 2015 3:49 PM