How to switch "Last, First" to "First Last" in Excel

The quick version: TEXTAFTER and TEXTBEFORE

Column A is a client list exported from a CRM, where every name came out as "Last, First". Mail merges, name badges and email greetings all need "First Last".

The comma is the key. Split on the comma, not on a space, and surnames like "de la Cruz" and first names like "Thi Lan" stay in one piece. Splitting on spaces would break both.

TEXTAFTER and TEXTBEFORE are in Excel for Microsoft 365, Excel for the web and Excel 2024. Put the formula in B2 and double-click the fill handle to copy it down the list.

Why ", " breaks on some rows (and the fix)

The formula above searches for a comma followed by a space. Row 3 was typed as "O'Brien,Sean" with no space, so ", " is never found and TEXTAFTER returns #N/A. Real exports always have a few rows like this.

The safer pattern searches for the comma alone and lets TRIM clean up whatever spacing is around it. TRIM removes leading and trailing spaces and squeezes runs of spaces down to one, so it also fixes rows like A6, which has spaces before the comma and after the name.

Messy spacing and multi-word names

A6 is " de la Cruz , Ana " with spaces all over the place. Because the split happens at the comma, the three-word surname survives intact, and TRIM strips the padding on both sides.

The same formula gives Thi Lan Nguyen for A4, Robert Smith Jr. for A5 and Chidi E. Okafor for A9. A suffix like Jr. sits before the comma, so it stays attached to the surname, which is where people expect to see it.

Names with no comma

A7 is "Chen Wei" with no comma. It may already be in the right order, or it may be a family-name-first name that should not be flipped at all. Either way, a formula cannot know, so the sensible rule is to leave it alone. Without a fix, TEXTAFTER returns #N/A and the MID/FIND version returns #VALUE!.

TEXTBEFORE and TEXTAFTER have a sixth argument, if_not_found. Give TEXTAFTER an empty string and TEXTBEFORE the original cell, and rows without a comma come back unchanged. The outer TRIM removes the extra space that is left at the front. Wrapping the earlier formula in IFERROR(...,TRIM(A2)) does the same job and is easier to read, but it also hides any other error.

Excel 2021 and older: MID, FIND and LEFT

Without TEXTAFTER, find the comma's position with FIND and cut around it. LEFT(A4,FIND(",",A4)-1) takes everything up to the comma. MID(A4,FIND(",",A4)+1,LEN(A4)) takes everything after it; using LEN(A4) as the length is a simple way to say "to the end".

For rows without a comma, wrap it in a test: =IF(ISNUMBER(FIND(",",A2)), formula above, TRIM(A2)). This works in every version of Excel, including Excel 2010.

ALL CAPS exports: add PROPER, then check the result

Some systems export names in capitals. Wrap the whole formula in PROPER to capitalise the first letter of each word. PROPER also capitalises after an apostrophe, so O'BRIEN becomes O'Brien.

PROPER does not know names, though. MCDONALD becomes Mcdonald, DE LA CRUZ becomes De La Cruz and VAN DER BERG becomes Van Der Berg. Only use PROPER when the source really is all capitals, and scan the result for Mc, Mac and particles like de, van and von before you send anything addressed to real people.

No formulas: Flash Fill

For a one-off clean-up, Flash Fill is fastest. It works in Excel 2013 and later. It copies the pattern you type, so give it a clean example and then check the rows that look different.

1. In B2, type the first name the way you want it: Maria Garcia.
2. Select B3 and press Ctrl+E (or Data > Flash Fill).
3. Check the odd rows: no-comma names, suffixes and multi-word surnames are where Flash Fill guesses wrong.

Which method to use

Use the TRIM(TEXTAFTER) formula in Excel 365 when the list will be refreshed, because it updates when the source changes. Use the MID/FIND version if anyone opening the file has Excel 2021 or older, since TEXTAFTER shows #NAME? there. Use Flash Fill for a one-time job, then delete column A.

If you need to replace the original column, copy the results and use Home > Paste > Paste Values over column A. Deleting column A while the formulas still point at it turns every result into #REF!.