To change names such as Mary Ann Smith to Smith, Mary Ann, put the original name in A2 and enter a formula in B2. In Microsoft 365 or Excel 2024, the formula can move the last word to the front and add a comma; you can then fill it down the column. This method assumes the final word is the surname, so check names with suffixes or unusual formats before using the results.
What You’ll Need
- An Excel workbook with the names in one column, one name per cell.
- A blank column for the converted names, so the original data stays available for checking.
- Microsoft 365 or Excel 2024 for the TEXTAFTER and TEXTBEFORE formula below. If you have an older version, use Flash Fill if it is available, or follow the version note in the steps.
Step-by-Step
- Check how your names are written. The main formula is for names entered as given name or names followed by a surname, such as Mary Smith or Mary Ann Smith. It treats the final word as the surname. If your source already contains a comma, such as Smith, Mary, do not run this formula on it as if it were a first-name-first entry. Decide which format you want before converting, and make a copy of the worksheet or workbook if the list matters.
- Set up a separate results column. If the names are in column A, add a heading such as Reversed name in B1. Keep the original names in column A. Using a separate column makes it easy to compare results and prevents accidental loss of the source data. For an Excel Table, you can add the heading in the next empty table column; Excel will usually extend the table to include it.
- Enter the formula in the first data row. With the original name in A2 and the result intended for B2, enter
=IF(TRIM(A2)="","",IFERROR(TEXTAFTER(TRIM(A2)," ",-1)&", "&TEXTBEFORE(TRIM(A2)," ",-1),TRIM(A2))). Press Enter. For Mary Ann Smith in A2, B2 should display Smith, Mary Ann. For Jordan Lee, it should display Lee, Jordan. TRIM removes extra spaces at the beginning and end and reduces repeated ordinary spaces between words. IFERROR leaves a single-word entry unchanged instead of displaying an error, and the first IF keeps an empty source cell empty. - Fill the formula down the list. Select B2 and drag the small square at the lower-right corner of the selection down to the last name. If the neighboring column has a continuous list, you can also double-click that square to fill down alongside it. Check the last few rows: a fill handle may stop early if there are blank cells in the adjacent column. In an Excel Table, entering the formula in the first data cell may fill the whole calculated column automatically.
- Review entries that need special treatment. Compare the results with the source, especially names with middle initials, compound surnames, suffixes such as Jr., or multiple spaces. The formula moves only the final word. For example, Alex de la Cruz becomes Cruz, Alex de la, not de la Cruz, Alex. Excel cannot reliably infer which words belong to a person’s surname, so edit these exceptions individually or use a separately maintained surname field.
- Use Flash Fill for a quick pattern-based alternative. If you do not have TEXTAFTER and TEXTBEFORE, type the desired result next to the first source name. For example, if A2 contains Mary Ann Smith, type Smith, Mary Ann in B2. Start typing the next converted example in B3; when Excel suggests a consistent set of results, review the preview and press Enter to accept. In Excel for Microsoft 365 on Windows, Ctrl+E can invoke Flash Fill. You can also use Data > Flash Fill on Windows. On Mac, use Data > Flash Fill when that command is available. Flash Fill is pattern recognition, not a live formula, so it will not automatically update when source names change; it may also need several examples for a mixed-format list. Excel for the web may not offer the same Flash Fill command, so use a formula there if the functions are supported.
- Keep the results as formulas or make them fixed text. Leave the formulas in place if the source names might change; the results will recalculate. If you need a fixed list, select and copy the converted cells, then paste values into a new location using the Paste Values option. Check that the pasted text looks right before deleting or replacing the original column. Formula results can be sorted or copied, but a fixed-value copy is often safer when sending the list to another system.
Common Mistakes
Assuming Excel knows which words make up a surname. A rule that moves the last word works well for many simple names, but it is not a universal name parser. A name such as Maria del Carmen Ruiz may have a compound surname, and an entry like Sam Taylor Jr. ends in a suffix rather than the family name. Check those rows rather than treating the formula’s output as authoritative.
Replacing the original column too soon. If you paste over the source before reviewing the conversion, it can be difficult to spot or undo mistakes later. Keep the source column until you have checked the results and saved a clean copy. Also confirm that you selected only the intended output cells when using Paste Values.
Expecting a formula to interpret commas already in the data. The formula above expects names without a comma. An entry like Smith, Mary does not follow that pattern and may produce an incorrect result. Separate already-reversed entries from first-name-first entries, or decide on a consistent input format before applying the conversion.
Copying the formula without adjusting the starting cell. The example refers to A2. If your names begin in a different column or row, change A2 to the actual first name cell, and place the formula in the corresponding row of the results column. When filled down, relative references adjust automatically.
Tips
Use a heading that describes the result, such as Last, First, so the output format is clear to anyone who opens the workbook. If the list is long, filter or sort the results column to bring unusual entries together for review. Pay attention to blank cells, single-word names, initials, and punctuation, as these are common places for a conversion rule to behave differently from what you expect.
For better handling of compound surnames, consider separating first name and surname into dedicated columns when the source data provides that information. Then you can build the display name from the correct fields instead of guessing based on spaces. If you receive names regularly, agree on a consistent source format and document any exceptions, such as suffix placement, before processing each new list.
When sharing the workbook with someone using an older version of Excel, remember that TEXTAFTER and TEXTBEFORE require Microsoft 365 or Excel 2024. Older Excel versions may show a function-name error instead of calculating the formula. Flash Fill or a manually reviewed conversion can be more compatible for a one-time task. Mac users can use the same formula in a supported Excel version; keyboard shortcuts and command availability can differ, so the Data tab is a useful place to look for Flash Fill.
Troubleshooting
The formula shows #NAME? Your Excel version may not include TEXTAFTER and TEXTBEFORE. These functions are available in Microsoft 365 and Excel 2024. Update Excel if possible, or use Flash Fill where available. Check that the function names and quotation marks were copied correctly as well.
The formula shows a different error or stays visible as text. Confirm the formula starts with an equals sign and that the cell is not formatted as Text. Set the cell format to General, then re-enter the formula. Excel installations with regional settings that use semicolons as argument separators may require semicolons in place of the commas between formula arguments. The comma inside the quoted text, which is the punctuation inserted between surname and given names, should remain a comma.
The output has an unwanted word or a missing part. The formula moves the last space-delimited word and keeps everything before it together. That behavior explains results such as Cruz, Alex de la for Alex de la Cruz. Correct the exceptional result by hand, or use separate fields for the given name and full surname. Check whether a suffix such as Jr. is being treated as the surname; it will be, because it is the final word.
Extra spaces or nonbreaking spaces remain. TRIM handles ordinary spaces but does not remove every character that looks like a space. Data copied from websites or other systems may contain nonbreaking spaces. Use Find and Replace to replace the unusual space if you can identify it, or clean the source data before applying the formula. Inspect the source cell closely if names that look identical produce different results.
Flash Fill produces an inconsistent pattern. Undo or clear the incorrect results, then provide a few representative examples before invoking Flash Fill again. Include examples with middle names or initials only if you want those handled the same way. Flash Fill follows patterns it detects; it does not know the correct legal or cultural name structure, so inspect the complete output column.
Frequently Asked Questions
How do I change First Last to Last, First in Excel?
Place the source name in A2 and use the formula in the steps in B2. It moves the final word to the front, adds a comma and keeps any earlier words together after the comma.
Does the formula work with middle names?
Yes, if the surname is the final word. For example, Mary Ann Smith becomes Smith, Mary Ann. It cannot determine that a surname itself contains multiple words, so check compound surnames separately.
Can I reverse Last, First back to First Last?
Yes, but that is a different pattern. For a consistently formatted entry such as Smith, Mary, split at the comma and place the text after it before the text before it. Check spaces and entries with multiple commas, such as suffixes, before applying a conversion to the whole list.
Will the converted names update if I edit the originals?
Formula results update when their referenced source cells change. Flash Fill creates text results, so you need to run it again or edit the results manually after changing the source list.
Can I use this in Excel for Mac or Excel for the web?
The formula works in versions of Excel for Mac and Excel for the web that support TEXTAFTER and TEXTBEFORE. Menu locations and Flash Fill availability may vary. If Excel does not recognize either function, use a supported version or try Flash Fill where the command is offered.
How do I remove the formula but keep the names?
Copy the result cells and paste them using Paste Values. This replaces formulas with their displayed text. Verify the pasted names first, and keep the original names until you are certain the converted list is correct.
Related
- Find the Audit Trail in Excel: Changes and History
- Find and Delete Duplicates in Excel: A Step-by-Step Guide
- Find and Replace Hard Returns in Excel Step by Step
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- How to Divide in Excel: Formulas and ExamplesTo divide in Excel, enter a formula with a forward slash, such as =A2/B2, and press Enter. Replace…
- Excel: Merge Date and Time Cells Into One Cell With FormulaTo combine a date in A2 and a time in B2 into one Excel date-and-time value, enter =A2+B2…
- Excel Not Calculating? Fixes for FormulasIf your Excel formulas are not calculating, the most common cause is that calculation is set to Manual,…
- SUMIF Excel: Sum Cells Containing Specific TextTo sum values when a related cell contains particular text, use SUMIF with asterisks as wildcards. With descriptions…
- Excel Data Analysis Books: How to Choose the Right OneGood Excel data-analysis books include Microsoft Excel Data Analysis and Business Modeling by Wayne L. Winston for business…
- Excel Filter Shortcut: How to Add or Remove FiltersIn Excel for Microsoft 365 on Windows, press Ctrl+Shift+L to add filter drop-downs to the current data range;…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.