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.