To change names such as Ava Chen to Chen, Ava in Excel, enter a pattern with Flash Fill or use a formula in a new column, check the results, then copy and paste them as values if you need a permanent replacement. Excel has no single name-format switch: the right method depends on whether you want to reorder names, change capitalization, or both. The steps below are for Microsoft Excel for Microsoft 365 on Windows.
What You’ll Need
- A workbook with names in a consistent format, such as First Last, or separate first-name and last-name columns.
- An empty column for the reformatted results. Keeping the source names intact makes it easier to review and undo mistakes.
- For the formula method using TEXTBEFORE and TEXTAFTER, Excel for Microsoft 365 or Excel 2024. Flash Fill and the separate-column formula work for many earlier Excel versions too.
If the names are important records, save a copy of the workbook before changing them. Names can include middle names, particles, compound surnames, suffixes, or culturally specific capitalization, so automated results should be checked rather than assumed correct.
Step-by-Step
- Decide what the new format should be. Identify whether you need to reverse the order, adjust capitalization, or do both. For example, “Ava Chen” can become “Chen, Ava,” while “AVA CHEN” can become “Ava Chen.” If your source uses separate columns, note which column contains each name part. This decision matters because changing letter case alone will not move the surname, and reversing a name does not necessarily fix capitalization.
- Make a working copy of the names. Insert a blank column beside the source names: right-click the column letter where the new column should go and select Insert. Add a clear heading such as “Formatted name.” Do not type over the original list yet. If the cells are part of an Excel Table, add the heading in the next table column; Excel will extend the table automatically. A separate output column lets you compare each result with its source.
- For separate first-name and last-name columns, join them in the desired order. Suppose first names are in A2:A100 and last names are in B2:B100, and the output belongs in C2. Enter
=B2&", "&A2. For example, if A2 contains Ava and B2 contains Chen, C2 returns “Chen, Ava.” Press Enter, then select C2 and double-click the fill handle, the small square at its lower-right corner, to fill the formula down alongside the existing records. Use a space instead of the comma if your preferred output is “Chen Ava.” - For a single First Last column, build the reversed format with a formula. Assume full names are in A2:A100, with ordinary spaces between the name parts and the final word representing the surname. In B2, enter
=IFERROR(TEXTAFTER(TRIM(A2)," ",-1)&", "&TEXTBEFORE(TRIM(A2)," ",-1),TRIM(A2)). If A2 is “Ava Marie Chen,” the result is “Chen, Ava Marie.” The formula takes the final word as the surname and leaves everything before it in the given-name portion. TRIM removes extra spaces at the beginning or end, and IFERROR returns a trimmed single name unchanged if there is no space to split at. Fill the formula down using the fill handle. This formula requires Microsoft 365 or Excel 2024. - Use Flash Fill when you prefer to show Excel an example. In the output column, type the correct result next to the first source name. For example, beside “Ava Chen,” type “Chen, Ava.” Start entering the next result in the cell below. Excel may show a preview of the pattern; if it matches the list, press Enter to accept it. If no preview appears, select the output cells and choose Data > Flash Fill. On Windows, Ctrl+E also runs Flash Fill. On Mac, use Data > Flash Fill if the keyboard shortcut differs. Review the results carefully, especially when the list mixes middle names or different name conventions.
- Change capitalization separately if needed. For names in A2:A100, enter
=PROPER(A2)in B2, then fill it down. If A2 is “AVA CHEN,” the result is “Ava Chen.” PROPER changes each word to initial-capital form; it does not reorder names. It may not preserve intended styling in names such as “McDonald,” “O’Neil,” or names with internal capitals, so inspect those entries and correct them manually where appropriate. You can use the capitalization formula on a result column after you have reordered the names, but check the comma-separated format as well. - Check, then make the output permanent if required. Compare a sample from the top, middle, and bottom of the list, including any unusual names. When the results are correct, select the output cells and press Ctrl+C. Right-click the selected destination or original cells and choose Paste Special > Values. This replaces formulas with text, so the results no longer change when source cells change. Keep the original column until you have confirmed that the pasted values are correct. If you only need a different display for a report, leave the source column untouched and use the output column instead.
For a short list, you can also type each corrected name directly into the output column. That is often safer than applying a pattern to a list containing inconsistent formats, but it is slower for large ranges. Whichever method you choose, retain one row per person and avoid sorting only the output column; sort the entire table or selected data range so names stay with their associated information.
Common Mistakes
- Assuming Excel knows which part is the surname. A formula that treats the final word as the surname will misread a compound surname such as “van der Meer,” and it cannot reliably infer name order from “Jordan Lee.” Confirm the source convention before applying it.
- Using PROPER to reverse the order. PROPER changes letter case only. “AVA CHEN” becomes “Ava Chen,” not “Chen, Ava.” Use a reordering formula, Flash Fill, or separate columns to move name parts.
- Overwriting the source too early. Formula results may look right in a few rows but fail on a suffix or an unusual entry. Keep the original values until the complete output has been reviewed.
- Filling only part of the data or sorting one column. Check that every relevant row has a result. When sorting, include the whole table or range so addresses, IDs, and other details do not become attached to the wrong person.
- Expecting automatic capitalization to preserve every spelling. PROPER follows a general rule, not a person’s preferred spelling. Review internal capitals, apostrophes, hyphens, and accents.
Tips
- Use a consistent format for the whole output column, including punctuation and spacing. Decide in advance whether you want “Chen, Ava” or “Chen, Ava Marie,” and whether suffixes such as Jr. should remain at the end.
- Test formulas on a few representative rows before filling the entire list. Include a middle name, an extra space, a single-word entry, and any suffixes or compound names present in your data.
- For repeatable workbooks, keep source names and formatted names in separate columns. This allows you to regenerate the output if source data changes and makes the transformation visible to other people.
- Use Paste Special > Values only after you are satisfied with the results. If the output is for a one-time report, formulas can remain in place; if it must be sent as fixed text, values avoid formula links to the original column.
Troubleshooting
The formula shows #NAME? Your Excel version may not include TEXTAFTER or TEXTBEFORE. Those functions are available in Microsoft 365 and Excel 2024. Use Flash Fill from Data > Flash Fill, or split the source into first- and last-name columns using Data > Text to Columns if the names follow a consistent delimiter. Text to Columns changes the source layout, so work on a copy or destination range.
The result has the wrong surname or missing name parts. The formula assumes the last word is the surname and all preceding words belong in the given-name portion. For surnames with multiple words, suffixes, or names entered surname-first already, correct those rows manually or use a tailored rule. A formula cannot reliably determine a person’s intended name structure from text alone.
Flash Fill creates an unexpected pattern or does nothing. Give Excel a clear example in the first output row and, if needed, a second example that demonstrates how a middle name or suffix should be handled. Then select the output range and choose Data > Flash Fill. If Excel still cannot infer the pattern, use a formula for consistent data or enter the results manually. Do not accept a preview without checking the entire column.
Extra spaces remain, or names display with the wrong capitalization. The formula above trims spaces at the ends, but it does not repair every inconsistent space inside a name. Clean irregular entries individually, and treat capitalization as an editorial decision. If you have replaced the source with pasted values, use Undo immediately if the change is still reversible; otherwise restore the saved copy or re-import the original list.
Frequently Asked Questions
Can Excel change First Last to Last, First automatically?
Yes. Use Flash Fill with an example, or use the formula in this guide for a consistent list where the last word is the surname. Review names with compound surnames or suffixes because Excel may not parse them as intended.
How do I change all-uppercase names to title case?
Use =PROPER(A2) in a new column when the original name is in A2, then fill the formula down. Check names with internal capitals, apostrophes, or unusual capitalization before replacing the source.
Can I combine first and last names from different columns?
Yes. If the first name is in A2 and the surname is in B2, enter =A2&" "&B2 to return “Ava Chen.” Reverse the cell references and add a comma if you want “Chen, Ava.”
Will changing the name format change the original data?
Not if you put the formula or Flash Fill result in a separate column. Formulas update when their source cells change. To make a fixed copy, copy the results and use Paste Special > Values.
Does this work in Excel for Mac or Excel for the web?
Most of the steps and current Microsoft 365 formulas also work in Excel for Mac and Excel for the web, though ribbon placement and shortcuts can vary. On Mac, use the Data tab’s Flash Fill command if Ctrl+E is not available. Check the function availability in your specific Excel version if TEXTBEFORE or TEXTAFTER is not recognized.
Related
- How to Add Page Numbers in Excel
- How to Add a Row in Excel: Shortcuts for Windows and Mac
- How to Add a Search Bar in Excel for Your Worksheet
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Text Join in Excel With a Comma: Formula and ExamplesTo join the values in cells A2:A10 with a comma and a space, use =TEXTJOIN(", ",TRUE,A2:A10). The formula…
- IF Function in Excel: Examples and Practical Uses ExplainedThe Excel IF function checks whether a condition is true and returns one result if it is, and…
- Excel Concatenate String Examples: Formulas That WorkTo concatenate strings in Excel, join cell references and text with the ampersand operator (&), or use CONCAT…
- How to Copy a Formula Horizontally in ExcelTo copy a formula horizontally in Excel, select the cell that contains it and drag the small fill…
- Excel SUMIFS: Multiple Criteria in the Same Column?Yes. In Excel, use SUMIFS with the same criteria range more than once when every condition must be…
- Excel Highlight Duplicates With Unique Colors?Yes—but Excel’s built-in duplicate-highlighting command applies one format to every duplicate, not a different color to each repeated…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.