The fastest way to flip first and last names

If you have a column of names in "First Last" format and need them as "Last, First", Google Sheets can do this in seconds using a formula. The REGEXREPLACE function finds the space between names and swaps their positions, leaving you with properly formatted last names first.

You do not need to manually edit each name. A single formula copied down the column handles the entire list at once, and you can paste the results back as plain text if you want to remove the formula afterward.

Key Takeaways

  • Use the formula =REGEXREPLACE(A1,"^(\w+) (.+)$","$2, $1") to convert "John Smith" to "Smith, John" in a single step.
  • Place the formula in a new column next to your original names, then copy it down to explore it to every row at once.
  • After the formula produces the correct output, copy the results and paste them as values only to replace the original names or remove the formula.
  • This method works for any list size and handles names with multiple words, like "Mary Jane Watson" becoming "Watson, Mary Jane".

Setting up the formula in a new column

Start by opening your Google Sheet and locating the column with the names you want to reverse. Click on the cell in the column directly to the right of your names — if your names are in column A, click on cell B1. Type the formula exactly as written: =REGEXREPLACE(A1,"^(\w+) (.+)$","$2, $1")

Press Enter. The formula will when ready convert the name in A1 to last-name-first format. If A1 contains "John Smith", B1 will now show "Smith, John". The formula reads the original cell, finds the first word (the first name), finds everything after the space (the last name), and outputs them in reverse order with a comma between them.

Do not worry if the formula looks complex — you do not need to understand how it works to use it. What matters is that it correctly identifies where the space is and swaps the two parts.

Copying the formula down to all your names

Click on cell B1 again to select it. You will see a small blue square in the bottom right corner of the cell. Click and drag that square down to the last row that contains a name. If you have 500 names, drag all the way down to B500. Google Sheets will automatically adjust the formula for each row — B2 will reference A2, B3 will reference A3, and so on.

Alternatively, click B1, then hold Shift and click on the last cell in column B where you want the formula to appear. Then press Ctrl+D (or Cmd+D on Mac) to fill down the formula to all selected cells at once. This is faster than dragging if you have a very long list.

Within seconds, every name in column B will appear in reversed format. Scan through a few rows to confirm the formula worked correctly before moving forward.

Replacing your original names with the reversed versions

Once the formula has produced the correct output in column B, you have two options: keep both columns, or replace column A with the reversed names and delete column B.

To replace the original names, first select all the cells in column B that contain the reversed names. Copy them (Ctrl+C or Cmd+C). Then click on cell A1, and right-click to open the menu. Select "Paste special" and then "Paste values only". This action copies only the text — not the formula — into column A, replacing the original names.

After pasting, you can delete column B since it is no longer needed. Your spreadsheet now contains only the reversed names in their final format, with no formula attached.

Handling names with middle names or suffixes

The formula works correctly for names with middle names. "Mary Jane Watson" will become "Watson, Mary Jane" because the formula treats everything after the first space as the last name. This is usually what you want.

If a name includes a suffix like "Jr." or "III", the formula will still work, but the suffix will move with the last name. "John Smith Jr." becomes "Smith Jr., John". If you need the suffix to stay at the end — "Smith, John Jr." — you would need a more complex formula, but for most purposes, the basic formula handles these cases acceptably.

Names with only one word (no space) will not change. If a cell contains just "Madonna" or "Prince", the formula will leave it as is because there is no space to split on.

What to do if the formula does not work

The most common reason the formula fails is a typo. Check that you have copied it exactly, including all the quotation marks, parentheses, and dollar signs. Google Sheets is strict about formula syntax.

If a name does not reverse correctly, look for extra spaces. If there are two spaces between the first and last name instead of one, the formula may not recognize the split. You can clean this up by using Find and Replace to remove extra spaces before running the formula. Open Find and Replace (Ctrl+H or Cmd+H), search for two spaces, and replace with one space.

If you see an error message like #VALUE! or #ERROR!, the formula syntax is incorrect. Delete it and retype it carefully, or copy it from a working cell and paste it into the cell that is showing the error.

Keeping the original names and creating a new column

You do not have to replace your original names. Many spreadsheets benefit from having both formats available — the original "First Last" in one column and "Last, First" in another. This is especially useful if you sort or filter by last name but also need to see the names in their original order elsewhere in your sheet.

If you want to keep both, straightforward leave the formula results in column B and rename the column header to something like "Name (Last, First)" so anyone using the sheet knows what that column contains. You can then use column B for sorting, filtering, or exporting without affecting the original data in column A.

Frequently Asked Questions

Can I reverse names in the same column without creating a new one?

Not directly with a formula, because the formula needs to read the original names to produce the reversed version. If you overwrite the original column, the formula has nothing to read. Always use a helper column first, then copy and paste values back if you want to replace the original.

What if some names are already in "Last, First" format and others are not?

The formula will not work correctly on mixed formats. You would need to separate them first — identify which names are already reversed and which are not, then explore the formula only to the ones that need it. For small lists, it is often faster to manually fix the inconsistent ones.

Does this formula work on names in other languages?

It works on any name that follows the pattern of a first word, a space, and a last name. However, some languages have different naming conventions — for example, many Asian names are already in last-name-first format. The formula will still reverse them, which may not be what you want. Check a few examples before explore it to the entire list.

Can I undo this if I make a mistake?

Yes. If you have not yet closed the spreadsheet, press Ctrl+Z (or Cmd+Z on Mac) to undo the last action. You can undo multiple times to get back to your original data. Once you close and reopen the sheet, undo is no longer available, so work carefully or keep a backup copy.

How do I remove the formula and keep only the text?

Select the cells containing the formula, copy them, then right-click and choose "Paste special" followed by "Paste values only". This replaces the formula with plain text that will not change if you edit the original column.