Switch First and Last Name with Comma in Excel (Reverse Names)

If you’ve ever been tasked with organizing a list of names in Excel, you know how frustrating it can be to get the formatting just right.

A common format when it comes to names is to have the last name followed by the first name with a comma in between.

But in many cases, the data set that you get would have the names as first name and last name.

name dataset modifies to switch first and last name

This tutorial covers some simple methods you can use to quickly switch the first and the last name separated by a comma (flip the first and last name). The methods covered here include some inbuilt functionalities in Excel as well as formulas.

Method #1: Switch First and Last Name with Comma Using Flash Fill

The Flash Fill feature in Excel fills in data automatically if it detects a pattern in the data.

For example, we will use this feature to switch the first and last names in the following dataset, separating them with commas and spaces.

Names Dataset

We use the following steps to reverse the name and flip the first and the last name:

  1. Select cell B2 and enter the name in cell A2, beginning with the last name and then the first name. Separate the data with a comma and a space. 
Enter name in cell A2
  1. Select cell B3 and type in the name in cell A3, beginning with the last name and then the first name. Separate the data with a comma and a space.
Enter name in cell B3

Note: We have entered the data in cells B2 and B3 to show the Flash Fill feature the pattern it should use in filling in the rest of the data.

  1. Select range B2:B8.
Select the range
  1. Click the Data tab and Flash Fill in the Data Tools group (you can also use the keyboard shortcut Control + E.
click on Flash fill

The Flash Fill feature fills in the rest of the data in our indicated pattern.

Caution: Flash Fill is great at identifying patterns and works as expected in most cases. However, it’s a good idea to always cross check the data and make sure everything is right. If you have consistent data (i.e., first name and last name only), then Flash Fill would work perfectly. But in case there are some inconsistency in your data, there is a possibility that flash fill may give you incorrect results in some cells

If there are changes in the original data, we will have to repeat this process because the Flash Fill Feature does not update the new data. 

If we want the new data to update whenever there are changes in the original data, we can use the following methods.

Method #2: Switch First and Last Name with Comma Using RIGHT, LEN, SEARCH, and LEFT Functions

Excel has a lot of text formulas that can be used to quickly manipulate name datasets.

While there is no dedicated function that will allow us to flip the first and the last name and separate it by a comma, this can easily be done by a combination of functions

We will use a formula that combines the RIGHT, LEN, SEARCH, and LEFT functions to switch first and last names with commas in the following dataset.

Names Dataset

In our process, we follow the following steps:

  1. Select cell B2 and type in the following formula:
=RIGHT(A2,LEN(A2)-SEARCH(" ",A2))&", "&LEFT(A2,SEARCH(" ",A2)-1)
Enter formula in cell B2
  1. Click the Enter button on the Formula bar to enter the formula. Double-click or drag down the fill handle to copy the formula down the column.
Apply formula to the entire column

Explanation of the formula

=RIGHT(A2,LEN(A2)-SEARCH(” “,A2))&”, “&LEFT(A2,SEARCH(” “,A2)-1)

  • RIGHT(A2,LEN(A2)-SEARCH(” “,A2)) The SEARCH function returns the value 6, which is the position of the space character in the text string in cell A2. The LEN function returns the value 11, which is the text string length in cell A2. The value returned by the SEARCH function is subtracted from the value returned by the LEN function, and the result is the value 5. The RIGHT function uses this value 5 to return the 5-letter name, Simon.
  • LEFT(A2,SEARCH(” “,A2)-1) The value 6 returned by the SEARCH function is reduced by 1 to get the length of the first name which in this case is 5. The LEFT function uses this value 5 to return the 5-letter first name, Peter. 
  • The last name returned by the RIGHT function is concatenated with the first name returned by the LEFT function with a space and a comma between them.

Method #3: Switch First and Last Name with Comma Using MID, FIND, and LEN Functions

We will use a formula that combines the MID, FIND, and LEN functions to switch first and last names with commas in the following dataset.

Names Dataset

In our process, we follow the following steps:

  1. Select cell B2 and type in the following formula:
=MID(A2&", "&A2,FIND(" ",A2)+1,LEN(A2)+1)
Enter formula in cell B2
  1. Click the Enter button on the Formula bar to enter the formula. Double-click or drag down the fill handle to copy the formula down the column.
Apply formula to the entire column

Explanation of the formula

=MID(A2&”, “&A2,FIND(” “,A2)+1,LEN(A2)+1)

  • A2&”, “&A2 The text string in cell A2 that has a length of 11 characters is concatenated with the same text string with a comma and a space between them. A new text string of a length of 24 characters is created.
  • FIND(” “,A2)+1 This part of the formula returns the value 7, which is the starting position of the second name in cell A2.
  • LEN(A2)+1  This part of the formula returns the value 12.
  • The formula becomes =MID(Peter Simon, Peter Simon,7,12). The MID function returns 12 characters from the character in the seventh position of the new text string.

Method #4: Switch First and Last Name with Comma Using Text to Columns and CONCAT Function

We will split the names in column A into two columns, B and C, and then use the CONCAT function to switch the values in the two new columns. Finally, we will separate each name and combine them with commas and spaces in column D.

Names Dataset

Below are the steps to get this done:

  1. Select range A2:A8.
Select the names dataset range
  1. Click the Data tab and the Text to Columns tool in the Data Tools group.
click on text to columns

The Text Wizard determines correctly that our data is delimited. 

make sure delimited is selected
  1. Click the Next button on the Text Wizard dialog box.
  2. Select the Space option on the next screen of the Text Wizard and click Next.
check the space option
  1. Type =$B$2 in the Destination field and click the Finish button.
specify the destination cell

The data in column A is split into columns B and C:

names split into first and last name
  1. Select cell D2 and type in the following formula:
=CONCAT(C2,", ",B2)
Enter the CONCAT formula
  1. Click the Enter button on the Formula bar to enter the formula. Double-click or drag down the fill handle to copy the formula down the column.
Apply formula to the entire column

Method #5: Switch First and Last Name with Comma Using REPLACE, SEARCH, and LEFT Functions

We will use a formula that combines the REPLACE, SEARCH, and LEFT functions to switch first and last names in the following dataset.

In addition, we will put a comma and a space between each name.

Names Dataset

We use the following steps:

  1. Select cell B2 and type in the following formula:
=REPLACE(A2,1,SEARCH(" ",A2),"")&", "&LEFT(A2,SEARCH(" ",A2)-1)
Enter formula in cell B2
  1. Click the Enter button on the Formula bar to enter the formula. Double-click or drag down the fill handle to copy the formula down the column.
Apply formula to the entire column

Explanation of the formula

=REPLACE(A2,1,SEARCH(” “,A2),””)&”, “&LEFT(A2,SEARCH(” “,A2)-1)

  • REPLACE(A2,1,SEARCH(” “,A2),””) The REPLACE function uses the value returned by the SEARCH function to replace the first name in cell A2 and the space after it with an empty string, leaving only the last name.
  • LEFT(A2,SEARCH(” “,A2)-1) The LEFT function uses the value returned by the SEARCH function to return the first name.
  • The last name returned by the REPLACE function is concatenated with the first name returned by the LEFT function with a comma and a space between them. 

Method #6: Switch First and Last Name with Comma Using VBA Macro

We will create a sub-procedure in Excel VBA and use it to switch the first and last names in the following dataset and put a comma and a space between them.

Names Dataset

In our process, we follow the following steps:

  1. Select the names dataset, click the Developer tab and the Visual Basic icon in the Code group. This opens the Visual Basic Editor.
Click on Visual basic
  1. Open the Insert menu and choose the Module item. This inserts a new module in the Visual Basic Editor.
Insert a module
  1. Type the following sub-procedure in the module:
'Code developed by Steve Scott from https://spreadsheetplanet.com

Sub SwitchNames()

Dim spaceLocator As Long

For Each Cell In Selection
    spaceLocator = InStr(Cell.Value, " ")
    If spaceLocator > 0 Then
        Cell.Value = Trim$(Mid$(Cell.Value, spaceLocator + 1)) _
        & ", " & Left$(Cell.Value, spaceLocator - 1)
    End If
Next Cell

End Sub
  1. Save the sub-procedure and save the workbook as a Macro-Enabled Workbook.
  2. Place the cursor anywhere in the procedure and press F5 to run the code.
  3. Click the View Microsoft Excel button on the toolbar. You are taken back to the active worksheet containing the dataset.
Click the View Microsoft Excel button

The first and last names have been switched and a comma and a space added between them.

Names dataset where names have been reversed

Note: Changes done by the VBA code can not be undone, so make sure that you create a backup copy of your data before you run this VBA macro code to switch the first and the last name separated by a comma

In this tutorial, I have covered various methods you can use to quickly switch the first and the last name in separate them by a comma.

One of the fastest ways to do this is by using Flash Fill which recognizes the patterns and fills the entire column with the desired output once you manually enter the desired output in one or two cells.

I also showed three separate formula combinations that you can use to quickly switch the first and the last name.

If this is something you need to do quite often, it would be a good idea to keep these formulas in your back pocket and use them to quickly modify the names data set.

And finally, I’ve covered a VBA method that would instantly give you the desired result with a single click.

The VBA method is more suited in case you need to do this quite often, where you can save the VBA code in your personal macro workbook and use this on any Excel workbook on your system.

Other Excel articles you may also like: