How to Switch First and Last Name in Excel with Comma

In the following image, we have switched the first and last name in Excel with comma.

We will use the following dataset to show how to switch first and last name in Excel with a comma. Here, column B contains the first and last names of 5 people.

Method 1 – Using Flash Fill to Switch the First and Last Name in Excel with a Comma

Case 1.1 – Using Flash Fill from the Home Tab

Steps:

• Select cell C5 and input the name like this: Smith, Emily.

Note: Be careful about the consistency of the pattern in your data. For example, you must have only the First Name and Last Name in cells. If all the cells don’t have a similar pattern, then Flash Fill won’t identify the pattern correctly. That will result in giving wrong outputs. So, it’s a good practice to cross-check the data.

• Go to the Home tab.
• Click on the Fill dropdown.
• Select the Flash Fill command.

• You will get the First Name Last Name switched to the format Last Name, First Name throughout the column.

Notes:

1. You can also choose the Flash Fill option from the Data tab.

2. Alternatively, you can use the keyboard shortcut Ctrl + E for Flash Fill. You need to have the range selected.

Case 1.2 – Using Flash Fill from the Fill Handle Tool

Steps:

• Select cell C5 and input the nam in the format you need.

• Hover over the bottom-right corner of the cell C5.
• The cursor will be changed from the White Plus sign to Green Plus. This Green Plus is our Fill Handle feature.
• Click and drag down until you reach the last row of the data
• Click on the Auto Fill Options drop-down icon.
• Choose the last option, Flash Fill.

Note: The Flash Fill method is not dynamic. If you change the original data, the cells where you used Flash Fill will not be changed automatically. You have to reuse Flash Fill after changing any data.

Method 2 – Using Excel Formulas to Switch the First and Last Name in Excel with Comma

Case 2.1 – Combining RIGHT, LEN, SEARCH, and LEFT Functions

Steps:

• Insert the following formula in cell C5 and press the Enter button.

• Drag the Fill Handle down to AutoFill the formula.

We have switched the first and last names with commas for the rest of the cells.

Note: Be aware of the extra spaces in the original dataset. This formula works by searching a single space between the first and last name. So, if your data contains more than one space between the first and last names, it will give an incorrect result. You can use the TRIM function to eliminate the extra spaces. Just use TRIM(B5) instead of B5 in the above formula.

Case 2.2 – Using MID, SEARCH, and LEFT Functions

Steps:

• Insert the following formula in cell C5 and press the Enter button.

• Drag the Fill Handle and AutoFill the formula down.

Case 2.3 – Joining MID, FIND, and LEN Functions

Steps:

• Insert the following formula in cell C5 and press the Enter button.

• Drag the Fill Handle to AutoFill the formula down.

Case 2.4 – Combining MID, SEARCH, and LEN Functions

Steps:

• Insert the following formula in cell C5 and press the Enter button.

• Drag the Fill Handle down and AutoFill the formula through the column.

Case 2.5 – Using REPLACE, SEARCH, and LEFT Functions

Steps:

• Insert the following formula in cell C5 and press the Enter button.

• Drag the Fill Handle down to AutoFill the formula.

Case 2.6 – Combining CONCAT, RIGHT, LEN, FIND, and LEFT Functions

Steps:

• Insert the following formula in cell C5 and press the Enter button.

• Drag the Fill Handle down to AutoFill.

Case 2.7 – Joining CONCAT, TEXTAFTER, and TEXTBEFORE Functions

Note: The TEXTAFTER and TEXTBEFORE functions are only available in the Microsoft 365 version.

Steps:

• Insert the following formula in cell C5 and press the Enter button.

• Drag the Fill Handle down to AutoFill the column.

Case 2.8 – Using RIGHT, LEFT, LEN, SEARCH, SUBSTITUTE, and CONCAT Functions (If a Middle Name Is Present)

We have a middle name in the values, so we’ll extract the last name and first name in C and D columns by using combined functions. We’ll use those values to make the final result.

Steps:

• Insert the following formula in cell C5 and press the Enter button.

• Drag the Fill Handle to AutoFill the formula down.

• Insert this formula in cell D5 and press Enter.

• AutoFill the formula down.

• Use this formula in cell E5 and press Enter.

• Drag the Fill Handle down to AutoFill.

Case 2.9 – Using the TEXTSPLIT Function with Other Functions

Steps:

• Insert the following formula in cell C5 and press the Enter button.

• Drag the Fill Handle to AutoFill down.

Method 3 – Using the Text to Columns Wizard and Excel CONCAT Function to Switch the First and Last Name in Excel with Comma

We’ll separate the first and last name then concatenate them with a comma.

Steps:

• Select the range B5:B9.
• Click on the Data tab.
• Click on the Text to Columns command.

• The Convert Text to Columns Wizard will appear with Step 1 of 3. The text wizard should detect that the data is Delimited.
• Click on Next.

• In Step 2 of 3, check the option Space under the field Delimiters.
• Click on the Next option.

• Select the General option under the Column data format field.
• Select the cell location (C5, the first cell of the First Name column) where you want to get the values under the Destination field.
• Click on Finish.

• The text data of column B is split into columns C and D.

• Use this formula in cell E5 and hit the Enter button.

• Use the AutoFill feature to fill the formula for the rest of the cells.

Read More: How to Reverse Names in Excel

Method 4 – Using Power Query to Switch the First and Last Name in Excel with Comma

We’ll use the following dataset. It contains 10 first and last names.

Steps:

• Select the dataset including the headers.
• Click on the Data tab and select From Table/Range.

• You will get a Create Table dialog box.
• Under where is the data for your table? field, the selected data range is showing.
• As we have selected the dataset including the header, My table has headers option should be checked.
• Press the OK button.

• You will get a table in Power Query Editor created with the selected range.

• In Power Query Editor, click on the Add Column tab and select the Column From Examples option.

• A new column named Column1 is added. Set examples in this column so that the Power Query can detect the pattern.
• Write Johnson, Sarah in the first row of the column.

• Insert another example in the second row and press Enter.
• The other cells will show suggestions.

Note: Check the suggestions properly. If it doesn’t match your requirements, you have to give more examples. Keep providing examples until it matches your pattern properly.

• Press the OK button or use the keyboard shortcut Ctrl + Enter to apply the suggested values.

Note: You can start providing the examples from any row of the Custom Column.

• You will get the first and last names switched with commas.

• Double-click on the column header and rename it however you want.

• Right-click on the first column and select Remove.

• Click on the Home tab and select Close & Load, then choose the Close & Load To option.

• The Import Data dialog box will appear. The Table option is selected by default.
• Select Existing Worksheet.
• Select the cell where you want to load the table.
• Click on the OK button.

• You will get the switched first and last names in your worksheet.

Method 5 – Using Power Pivot

We’ll use the same dataset.

Steps:

• Select the entire range where you have your datase.
• Click on the Power Pivot tab.
• Select the Add to Data Model option.

Note: In case you don’t have the Power Pivot tab, enable the Power Pivot add-in first in Options.

• The Create Table dialog box will appear. As we have selected the range before, it is showing already.
• Press the OK button.

• You will see the selected column in the Power Pivot window.

• Double-click on the table name which is set as Table1 by default.

• Rename this table. We renamed it as Switch_Names.

• Click on Add Column.

• Use this DAX formula in the formula bar of the newly added column and press Enter.

Note: This formula is similar to the formula we used in Excel worksheet using text functions. Power Pivot has a formula language known as DAX. The difference is Excel formulas work for a single cell at a time. But DAX formula works for the entire column.

• Double-click on the column header and rename it. We have renamed it as Last Name, First Name.

• Select the calculated column.
• Click on the Home tab.
• Select the PivotTable dropdown.
• Select the PivotTable option.

• The Create PivotTable dialog box will appear. The option New worksheet is selected by default.
• Press OK.

• A new Excel worksheet will open, and you have to choose the fields from the PivotTable field list.

• Choose the field Last Name, First Name from the Switch_Names table.

We have created the PivotTable where we get the first and last names switched with commas.

Method 6 – Applying VBA Code to Switch the First and Last Name in Excel with a Comma

Here’s the dataset we’ll use.

Steps:

• Click on the Developer tab. If you don’t have the Developer tab in Excel Ribbon, you have to enable it from Excel Options.
• Select the Visual Basic option.

Note: Alternatively, you can press Alt + F11 to open the VBA editor.

• Excel will lead you to the VBA Editor Window.

• Select Insert and click on Module.

• Copy the following code into the module and save the file.

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

VBA code to switch first and last name in Excel with comma

Note: Don’t forget to save your workbook as a Micro-Enabled Workbook.

• Go back to the Excel worksheet and select the range for which you want to switch first and last name with comma.
• Select the Developer tab.
• Select the Visual Basic option.

• Click on Run and choose Run Macro or use the keyboard shortcut F5 to run the code.

• Go to the Excel worksheet and you should get the results.

Note: If you run the VBA code once, you cannot undo this action. Store the backup copy of the original dataset before running the VBA macro.

Note: You can save this VBA code in your personal macro enabled workbook. Then, add the macro to the quick access toolbar with a relevant icon. By doing this, you can use this code multiple times just by clicking on the macro icon from the quick access toolbar.

Download the Practice Workbook

Related Readings

• How to Reverse a String in Excel
• How to Use Excel VBA to Reverse String
• How to Reverse a Number in Excel
• How to Paste in Reverse Order in Excel
• How to Reverse Rows in Excel

<< Go Back to Excel Reverse Order | Sort in Excel | Learn Excel