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.
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.
We use the following steps to reverse the name and flip the first and the last name:
- 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.
- 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.
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.
- Select range B2:B8.
- Click the Data tab and Flash Fill in the Data Tools group (you can also use the keyboard shortcut Control + E.
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.
Also read: How to Add Comma Between Names in Excel
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.
In our process, we follow the following steps:
- Select cell B2 and type in the following formula:
=RIGHT(A2,LEN(A2)-SEARCH(" ",A2))&", "&LEFT(A2,SEARCH(" ",A2)-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.
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.
In our process, we follow the following steps:
- Select cell B2 and type in the following formula:
=MID(A2&", "&A2,FIND(" ",A2)+1,LEN(A2)+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.
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.
Below are the steps to get this done:
- Select range A2:A8.
- Click the Data tab and the Text to Columns tool in the Data Tools group.
The Text Wizard determines correctly that our data is delimited.
- Click the Next button on the Text Wizard dialog box.
- Select the Space option on the next screen of the Text Wizard and click Next.
- Type =$B$2 in the Destination field and click the Finish button.
The data in column A is split into columns B and C:
- Select cell D2 and type in the following formula:
=CONCAT(C2,", ",B2)
- 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.
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.
We use the following steps:
- Select cell B2 and type in the following formula:
=REPLACE(A2,1,SEARCH(" ",A2),"")&", "&LEFT(A2,SEARCH(" ",A2)-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.
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.
In our process, we follow the following steps:
- Select the names dataset, click the Developer tab and the Visual Basic icon in the Code group. This opens the Visual Basic Editor.
- Open the Insert menu and choose the Module item. This inserts a new module in the Visual Basic Editor.
- 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- Save the sub-procedure and save the workbook as a Macro-Enabled Workbook.
- Place the cursor anywhere in the procedure and press F5 to run the code.
- Click the View Microsoft Excel button on the toolbar. You are taken back to the active worksheet containing the dataset.
The first and last names have been switched and a comma and a space added between them.
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:
