Table of Contents
To switch the first and last name with a comma in between in Excel, follow these steps:
1. Select the cell that contains the full name you want to switch.
2. Click on the “Data” tab in the menu bar.
3. In the “Data Tools” section, click on “Text to Columns”.
4. In the “Convert Text to Columns” wizard, choose “Delimited” as the file type and click “Next”.
5. Select “Comma” as the delimiter and click “Next”.
6. In the “Data Preview” section, you should see your full name separated into two columns.
7. Click on the first column and click “Move to the end” to switch the columns.
8. Click “Finish” to complete the process.
Your first and last name will now be switched with a comma in between.
Excel: Switch First and Last Name with Comma in Between
You can use the following formula to switch the first and last name in a cell in Excel and add a comma in between them:
=CONCAT(TEXTAFTER(A2," "), ", ",TEXTBEFORE(A2, " "))
This particular formula switches the first and last name in cell A2 and adds a comma in between them.
For example, if cell A2 contains Andy Evans then this formula will return Evans, Andy.
The following example shows how to use this formula in practice.
Example: How to Switch First and Last Name and Add Comma in Excel
Suppose we have the following column of first and last names in Excel:
Suppose we would like to switch the first and last name in each cell and add a comma in between them.
We can type the following formula into cell B2 to do so:
=CONCAT(TEXTAFTER(A2," "), ", ",TEXTBEFORE(A2, " "))
We can then click and drag this formula down to each remaining cell in column B:
Column B now displays the first and last name switched with a comma in between for each corresponding cell in column A.
For example:
- The formula returns Evans, Andy for Andy Evans.
- The formula returns Douglas, Bob for Bob Douglas.
- The formula returns Miller, Chad for Chad Miller.
And so on.
How This Formula Works
Recall the formula that we used to switch the first and last name of “Andy Evans” in cell A2:
=CONCAT(TEXTAFTER(A2," "), ", ",TEXTBEFORE(A2, " "))
Here is how this formula works:
First, the TEXTAFTER function extracts all text in cell A2 after the space. This returns Evans.
Then, the TEXTBEFORE function extracts all text in cell A2 before the space. This returns Andy.
Then we use the CONCAT function to concatenate together Evans with a comma and a space and Andy.
The end result is Evans, Andy.
This formula repeats this same process for each name in column A.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel: