# How to switch first and last names with a comma in Excel

Split at the last space and swap the parts: =TEXTAFTER(A2," ",-1)&", "&TEXTBEFORE(A2," ",-1) turns "Mary Ann Smith" into "Smith, Mary Ann".

**Excel:** `=TEXTAFTER(A2," ",-1)&", "&TEXTBEFORE(A2," ",-1)` (Microsoft 365 only)

**Google Sheets:** `=REGEXREPLACE(A2,"^(.*) (\S+)$","$2, $1")`

## Example

Sample data (row 1 is the header):

| A | B |
| --- | --- |
| Name | Last, First |
| Maria Lopez |  |
| James O'Neil |  |
| Mary Ann Smith |  |
| Lars Eriksen |  |
| Chidi Okafor |  |
| Aiko Tanaka |  |

Formula in B2, filled to B7: `=TEXTAFTER(A2," ",-1)&", "&TEXTBEFORE(A2," ",-1)`

Result: Lopez, Maria; O'Neil, James; Smith, Mary Ann; Eriksen, Lars; Okafor, Chidi; Tanaka, Aiko

## How it works

The last space separates the last name from everything before it. TEXTAFTER with `-1` returns the last name and TEXTBEFORE with `-1` returns the first and middle names, so "Mary Ann" stays together.

The Google Sheets formula does the same with one pattern: `(.*)` grabs as much as it can before the final space and `(\S+)` is the last word.

## Other ways

- `=TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",100)),100))&", "&LEFT(A2,LEN(A2)-LEN(TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",100)),100)))-1)`: Works in every version: the first part finds the last word, and LEFT keeps everything except that word and the space before it.

## Common mistakes

- Splitting at the first space instead puts middle names in the wrong place: "Ann Smith, Mary".
- Suffixes become the last name: "Robert Kim Jr." gives "Jr., Robert Kim".
- Trailing spaces break the split. Use `TRIM(A2)` inside the formula if the names were imported.

## Questions

**How do I sort by last name after switching?**

Sort the new column, or skip the helper column: `=SORTBY(A2:A7,TEXTAFTER(A2:A7," ",-1))`.

**How do I switch them without the comma?**

Use a space as the separator: `=TEXTAFTER(A2," ",-1)&" "&TEXTBEFORE(A2," ",-1)`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/switch-first-and-last-name-with-comma