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

Q: 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)).

Q: How do I switch them without the comma?

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

Excel formula

=TEXTAFTER(A2," ",-1)&", "&TEXTBEFORE(A2," ",-1)

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

Result on the example: one result per row in B2, filled down to B7
Works in: Microsoft 365 only
Google Sheets: Different formula, see below

See it work

The formula is already in B2, filled down to B7. Change any input and the result updates, or edit the formula to experiment.

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.

=TEXTAFTER(A2," ",-1)&", "&TEXTBEFORE(A2," ",-1)

Result"Lopez, Maria"

A2"Maria Lopez"
The value in A2.
-1-1
Makes the value negative.
TEXTAFTER(A2," ",-1)"Lopez"
TEXTAFTER: Text after a delimiter.
- textA2"Maria Lopez"
- delimiter" "
- instance_num-1-1
TEXTAFTER(A2," ",-1)&", ""Lopez, "
The & operator joins as text.
-1-1
Makes the value negative.
TEXTBEFORE(A2," ",-1)"Maria"
TEXTBEFORE: Text before a delimiter.
- textA2"Maria Lopez"
- delimiter" "
- instance_num-1-1
TEXTAFTER(A2," ",-1)&", "&TEXTBEFORE(A2," ",-1)"Lopez, Maria"
The & operator joins as text.

In Google Sheets

Google Sheets needs a different formula: =REGEXREPLACE(A2,"^(.*) (\S+)$","$2, $1"). On the same data it gives the same result. Here it is running in Google Sheets mode.

Other ways to do it

Each one was run on the same example sheet.

Formula	Result	When to use it
=TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",100)),100))&", "&LEFT(A2,LEN(A2)-LEN(TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",100)),100)))-1)	Lopez, Maria, O'Neil, James, Smith, Mary Ann…	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.

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Switch first and last names with a comma

In B2, rewrite the name as "Last, First" (the last word, a comma and a space, then the rest) and fill down to B7.

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).

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.