To flip a name such as John Smith to Smith John in Excel for Microsoft 365, enter a formula in a temporary helper cell, then paste its result over the original name. With the original name in A2, use =IFERROR(TEXTAFTER(TRIM(A2)," ",-1)&" "&TEXTBEFORE(TRIM(A2)," ",-1),TRIM(A2)) in B2. This moves the final word to the front; after checking the results, paste them as values into column A. Excel formulas cannot safely replace the value in the cell they refer to, so the helper column is the practical way to finish with the flipped names in the same cells.
The formula works for a two-part name and also moves the last word in a longer name to the front while keeping the earlier words in order. For example, Mary Ann Smith becomes Smith Mary Ann. Check how your organization writes compound surnames and suffixes before applying it to a whole list.
What You’ll Need
- Excel for Microsoft 365 on Windows. TEXTAFTER and TEXTBEFORE are available in Microsoft 365 and Excel 2024. Excel for the web also supports these functions.
- A column containing the names you want to change, plus an empty helper column beside it. This guide assumes names start in A2 and B is empty; adjust the references if your sheet uses different columns.
- A saved copy of the workbook or an undo plan. You can undo ordinary paste operations with Ctrl+Z, but keeping a copy is a safer way to protect important source data.
Step-by-Step
-
Check the name pattern and make a backup. Look at several rows, not just the first one. Confirm whether each name is stored as first name followed by surname, whether there are middle names, and whether any entries include titles or suffixes. Save the workbook before changing the data. If the list is important, duplicate the worksheet by right-clicking its tab and choosing Move or Copy, then selecting Create a copy.
-
Set up a helper column. If names are in A2:A100, use an empty column such as B. Put a descriptive header such as Reversed name in B1, or leave B1 empty if you do not use headers. Do not put the formula in A2: it refers to A2, and placing it there would create a circular reference rather than a usable result.
-
Enter the formula in B2. With the original name in A2, enter this formula in B2:
=IFERROR(TEXTAFTER(TRIM(A2)," ",-1)&" "&TEXTBEFORE(TRIM(A2)," ",-1),TRIM(A2))Press Enter. If A2 contains John Smith, B2 should show Smith John. TRIM removes extra spaces at the start or end and reduces repeated spaces between words. TEXTAFTER with -1 finds the text after the last space; TEXTBEFORE with -1 returns the text before that last space. IFERROR leaves a single-word entry as it is instead of showing an error.
-
Fill the formula down the list. Select B2 and point to the small square at the lower-right corner of the selected cell, called the fill handle. Drag it to the last row of names. If the adjacent column has continuous data, you can double-click the fill handle to fill down automatically; check where Excel stops, especially if there are blank rows. Alternatively, copy B2, select the rest of the destination range, and paste. Each row should refer to the name on its own row, such as A3 in B3.
-
Review the converted names. Compare the first, middle, and final rows in columns A and B. Check names with middle names, compound surnames, initials, blank cells, and suffixes individually. The formula moves the final word, not necessarily a person’s complete surname. For example, Ana de la Cruz becomes Cruz Ana de la. If that is not the format you need, do not replace the source column yet; use the alternative approaches or troubleshooting notes below.
-
Replace the original names with values. When the helper results are correct, select the filled cells in column B and copy them. Select the matching first cell in column A, such as A2, then use Home > Paste > Values. This pastes the displayed names as fixed text rather than formulas, so they no longer depend on the helper column. On Mac, use Home > Paste > Paste Values if that option is shown, or use Edit > Paste Special and choose Values. In Excel for the web, use the Paste Values option on the Home tab.
-
Confirm and clean up. Check that the names now appear in the original cells and that no rows were missed. If the results are right, delete the temporary helper column or clear its contents. Keep the backup sheet or file until you are confident the new order is correct. If you notice a problem before making further edits, use Ctrl+Z on Windows or Command+Z on Mac to undo the paste.
Common Mistakes
Trying to change a cell with a formula that refers to itself. A formula entered in A2 that also uses A2 creates a circular reference. Use a separate column for the calculation, then paste the result back as a value.
Copying formulas instead of values over the originals. If you paste B2:B100 normally into A2:A100, Excel may paste formulas whose references shift. The displayed names can change or become incorrect when the helper column is cleared. Use Home > Paste > Values so the destination receives text, not formulas.
Assuming the formula understands every kind of name. The formula treats the final space-separated word as the part to move. It does not identify surnames, titles, generational suffixes, or cultural name order. A name such as Jordan Lee Jr. would become Jr. Jordan Lee, which is usually not the intended result. Inspect exceptions before applying the formula to your full list.
Including a header in the formula range. If row 1 contains a heading such as Full name, start the formula at row 2 and paste results beginning at A2. Replacing the header can make later sorting and filtering confusing.
Overlooking extra spaces or empty rows. TRIM handles ordinary extra spaces in each name, but blank rows can affect automatic filling and might leave gaps. Check the bottom of the data and fill the formula through the exact range you need.
Tips
- Keep the source until you have checked the output. The helper column makes it easy to compare each original with its converted version. Do not delete it until the pasted values in the original column look right.
- Use filters or sorting to inspect exceptions. If the list is long, filter for entries with initials, suffixes, or extra words and review those rows separately. Sorting the helper results can also bring unusual entries together, but avoid sorting only one column; select the whole table so each name stays with its other information.
- Choose the correct rule for middle names. This formula places the last word first and leaves all preceding words in their original sequence. Thus, it changes Mary Ann Smith to Smith Mary Ann, not Smith Ann Mary. If you want to reverse every word or move only the first and last words while leaving middle names in place, treat that as a different transformation and test a suitable formula on sample rows first.
- Use Flash Fill for a small, one-off list. In a neighboring column, type the desired result for the first name, then start the next result. If Excel recognizes the pattern, choose Data > Flash Fill or press Ctrl+E on Windows. On Mac, use Data > Flash Fill if available; the shortcut can differ by keyboard and Excel version. Review all filled entries before replacing the source because Flash Fill infers a pattern rather than applying a rule you can audit in a formula.
- Keep a consistent output style. Decide whether the flipped names should use one space, preserve capitalization, or follow another house style. The formula preserves the letters’ existing capitalization, so JOHN SMITH stays SMITH JOHN and john smith stays smith john. It does not correct capitalization.
Troubleshooting
The formula displays #NAME? or Excel does not recognize a function. Check that you are using Microsoft 365 or Excel 2024. TEXTAFTER and TEXTBEFORE are not available in some older Excel editions. If your workplace uses an older version, ask which supported option is approved or use Flash Fill on a short list and inspect every result; do not assume a newer formula will work in that installation.
The formula is shown as text instead of calculating. Select the formula cell, set its format to General using Home > Number Format, then press F2 and Enter. Also check that the formula begins with an equals sign and that there is no apostrophe before it. Excel settings that use semicolons as argument separators may require semicolons instead of commas; the formula shown here uses comma separators for the standard English-language setup.
The result has an unexpected word at the front. The calculation moves whatever appears after the last space. Review names containing a suffix, title, or compound surname. If your data has a consistent suffix such as Jr., handle those rows separately or create a specific rule for the format you need. Do not paste the results over the originals until these exceptions are resolved.
Some rows did not fill or show a strange result. Confirm the fill range reaches the final name and that the formula references the correct source column. A blank source cell returns a blank result. If a row contains nonbreaking spaces copied from a website, TRIM may not remove them; replace the unusual spaces or clean the source text before filling the formula down.
The names changed after pasting. Undo with Ctrl+Z on Windows or Command+Z on Mac if possible. Repeat the copy and paste using Paste Values, and make sure you paste into the first matching cell in the source range. If undo is no longer available, restore the saved copy or backup sheet rather than trying to reconstruct names from memory.
Frequently Asked Questions
Can I flip names directly in the same cell with a formula?
No. A formula cannot replace its own input cell without creating a circular reference. Calculate the flipped name in a helper cell, then paste the result as a value into the original cell.
Does this formula work with middle names?
Yes, if your goal is to move the last space-separated word to the front and keep the earlier words in order. Mary Ann Smith becomes Smith Mary Ann. It does not identify which words form a person’s surname.
Will it handle a single name or a blank cell?
Yes. IFERROR returns the trimmed original text if there is no space to split, so a single name stays unchanged and a blank cell stays blank.
Can I use this in Excel for Mac or Excel for the web?
The formula works in Microsoft 365 for Mac and Excel for the web. The paste-values command may be arranged differently; look for Paste Values on the Home tab or use the Mac Edit > Paste Special menu.
How do I flip only first and last names when a middle name is present?
The formula in this guide moves the final word in front of all the others; it does not keep a middle name between first and last. Test the exact desired order on sample names and use a rule designed for your data layout before replacing the original column.
Related
- How to Enable Undo in Excel and Restore the Undo Button
- Track Changes in Excel Online: View and Review Edits
- How to Enable Transpose in Excel
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel IF Blank: Formulas for Empty Cells and ResultsIf you want Excel to show one result when a cell is blank and another when it is…
- Excel FILTER Function for Text: Examples and TipsWith records in A2:C6 and the text to match in B2:B6, use =FILTER(A2:C6, B2:B6="North", "No matching rows") to…
- Excel Workday Difference Between Two DatesIn Excel for Microsoft 365 on Windows, use =NETWORKDAYS(A2,B2) to count working days from the start date in…
- Excel Standard Deviation Formula: Which Function to UseIn Excel for Microsoft 365, use STDEV.S to calculate standard deviation from a sample, or STDEV.P when your…
- Excel Filter Data Validation List: Setup and LimitsYes—updated Microsoft 365 versions of Excel can autocomplete a data-validation dropdown as you type, narrowing the choices to…
- How to Drag a Number Sequence in Excel Without GapsTo drag a number sequence in Excel, enter the first two numbers in adjacent cells, select both cells,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.