Skip to main content

Switch first & last names in Excel: An easy complete guide

In many contexts, names are conventionally listed in the "First Name Last Name" format. However, there are instances where reversing this order to "Last Name, First Name" is preferable, placing the surname first, then a comma, and the given name last. Whether for organizational, database management, or stylistic reasons, flipping the arrangement of names can be necessary but potentially tedious if done manually across a large dataset.

This tutorial shows you how to easily rearrange names in Excel, whether you're flipping names from "First Name Last Name" to "Last Name, First Name" (with or without a comma) or the other way around. Whether you need straightforward swaps or more complex solutions involving advanced features or scripts, we've got everything you need.


Flip first and last names using Flash Fill

Excel's Flash Fill feature is a smart and efficient tool for automatically filling in data based on a pattern you provide. This makes it perfect for tasks like reversing the order of first and last names in your dataset. Follow these simple steps to use Flash Fill for flipping names:

  1. In the first cell of the empty column next to your names (e.g., B2), type the name from cell A2 in the reversed order: "Last Name, First Name".
  2. Select the next cell down in the same column, and enter the reversed name from cell A3. As you start typing, the Flash Fill function will recognize the pattern and automatically populate the remaining cells according to it.
  3. Press Enter to confirm the Flash Fill suggestions.

    Tip: As an alternative to steps 2 and 3, you can press Ctrl + E on your keyboard, or go to the Data tab on the Ribbon and click on the Flash Fill button to fill the cells below with the pattern you provided in B2.

Notes:

  • This method is equally effective if you prefer not to include a comma or if you wish to revert the order from "Last Name, First Name" back to "First Name Last Name". The key is to provide the initial pattern as you wish to see it replicated.

  • If some names have middle names and some do not, Flash Fill may not work as seamlessly in processing them. For better outcomes in these situations, refer to the AI method described in the next section.
  • Flash Fill provides a fixed solution; the names reversed through this method remain static, meaning they do not dynamically update with changes to the original names or the addition of new entries. For a dynamic solution that updates automatically, the formula method is recommended.

Switch first and last names using AI

Kutools for Excel features an AI-powered assistant, AI Aide, designed to effortlessly reverse the order of names, regardless of whether they include middle names. Utilizing the user-friendly interface of Kutools AI Aide, you can easily reverse the order of names in single or multiple selected ranges as needed. Please follow these steps:

Note: The Undo function is unavailable after using an AI operation. It's advisable to create a backup of your original data before proceeding with any AI-assisted modifications.

Download Kutools for Excel to access the feature and unlock your 30-day free trial!
  1. Navigate to the Kutools tab on the Excel ribbon and select AI Aide to open the Kutools AI Aide interface.
  2. Select the names you want to flip.
  3. Type your command clearly in the input box. For example, you can enter: "Move last names in selection to the front, followed by a comma".
  4. Press Enter or hit the Send button .

  5. Kutools AI Aide promptly processes your command, presenting a solution. Simply click the Execute button to apply the rearrangement across your selected data.

Result

All last names in the selected cells are moved to the front, followed by a comma.

Tips if the result does not meet your expectations:
  1. Simply click the Unsatisfied button. Kutools AI Aide will then undo the reverse and generate a detailed, step-by-step guide tailored to your needs.

  2. Consider rephrasing your command to see if it yields a more satisfactory result.

Note: Want to access the Kutools AI Aide? Download Kutools for Excel now! Beyond this, Kutools boasts a myriad of 300+ other features and offers a 30-day free trial. Don't wait, give it a try today!


Reverse first and last names using formula

To reverse first and last names in Excel using a formula, you can rely on Excel's text manipulation functions. This method is especially useful when you need a dynamic solution that updates automatically if the original names change. Follow these steps to implement the formula:

  1. Assume the names you want to reverse are in column A, starting from A2. Select the first cell in the column where you want the reversed names to appear (B2 in our case), and enter the formula:
    =REPLACE(A2,1,SEARCH(" ",A2),"")&", "&LEFT(A2,SEARCH(" ",A2)-1)
    Tip: In the provided formula, ensure you replace A1 with the actual cell reference that contains the name you wish to reverse.
  2. Drag the fill handle at the bottom right corner of cell B2 downwards to apply the formula to all relevant cells.

Tips:
  • If you prefer not to include a comma but only a space, consider using the below formula:
    =MID(A2&" "&A2,FIND(" ",A2)+1,LEN(A2))

  • If you wish to revert the order from "Last Name, First Name" back to "First Name Last Name", use the following formula:
    =MID(A2&" "&A2,FIND(", ",A2)+2,LEN(A2)-1)

(AD) Reverse text order easily with Kutools for Excel

Struggling to reverse text in your Excel cells, whether flipping each letter individually or rearranging text around a specific separator? Kutools for Excel makes it easy!

With the Reverse Text Order feature, you can quickly invert letters inside a cell or reverse text segments separated by your chosen delimiter - all through a single, straightforward dialog box!

Kutools for Excel: Integrating AI 🤖, 300+ handy Excel functions at your fingertips. Try them all in a 30-day free trial with no feature restrictions now!


Switch first and last names using Power Query

Using Power Query in Excel is a robust method for manipulating and transforming data, including switching the positions of first and last names while inserting a delimiter of your choice. Follow these detailed steps to accomplish this task with Power Query:

Note: Opt for the Power Query method exclusively in specific scenarios: if Power Query is already a part of your data transformation workflow and you need to integrate name reversal, or if you frequently face similar tasks. For one-time name reversal needs, simpler alternatives such as Flash Fill, AI, or formulas (as explored earlier in this guide) are likely more suitable and direct solutions.

  1. Select the range that contains the names you want to reverse.
  2. Navigate to the Data tab and click on From Table/Range in the Get & Transform Data group.
  3. If your data isn't in a table format, Excel will prompt you to create one. Please click OK.

  4. In the Power Query Editor that opens up, right-click on the header of the name column and then select Split Column > By Delimiter.

  5. Choose the delimiter separates your names (typically a space) and opt to split at each occurrence of the delimiter, then click OK.

  6. After splitting, you'll have two columns for first and last names. Manually drag the column holds the last name to be the first column.

  7. Select the columns you want to merge by holding down the Ctrl key and clicking each one. Then, right-click on any of the column headers and choose Merge Columns.

  8. In the Merge Columns dialog, from the Separator drop-down list, select the Custom option and input a comma followed by a space (, ) for the separator. If needed, specify a name for the merged column, then click OK.

  9. Once satisfied with the preview, click the Close & Load button in the Power Query Editor to apply your changes.

Result

Power Query will instantly output the transformed data into a new worksheet in Excel.

Tip: This method creates a link between the original data and the transformed output. Any updates to the original data can be easily applied to the transformed output by refreshing: simply right-click the output table and choose Refresh.


Swap first and last names using VBA

You can also use VBA macros to flip first and last names in a column quickly. This approach is ideal for anyone favoring a coding method to manipulate data within Excel efficiently.

Note: Before running this VBA script, it's strongly recommended to create a backup of your data, since VBA operations cannot be undone with the standard Excel undo feature.

  1. Hold down the Alt + F11 keys in Excel to open the Microsoft Visual Basic for Applications window.
  2. Click Insert > Module, and paste the following macro into the Module window.
    Sub FlipName()
    'Update by ExtendOffice on 20240327
    Dim xRng As Range
    Dim xWorkRng As Range
    Dim xSign As String
    Dim xSeparator As String
    On Error Resume Next
    xTitleId = "Kutools for Excel"
    Set xWorkRng = Application.Selection
    Set xWorkRng = Application.InputBox("Flip names in the range:", xTitleId, xWorkRng.Address, Type:=8)
    xSign = Application.InputBox("Input the separator used within names:", xTitleId, Type:=2)
    For Each xRng In xWorkRng
        xValue = xRng.Value
        NameList = VBA.Split(xValue, xSign)
        If UBound(NameList) = 1 Then
            xRng.Value = NameList(1) & ", " & NameList(0)
        End If
    Next
    End Sub

    Note: This macro automatically inserts a comma between the reversed names. If you wish to use a different separator, you can modify the snippet xRng.Value = NameList(1) & ", " & NameList(0) found in the 16th line of the code. For instance, replacing ", " with " " will separate the names with a space instead of a comma.

  3. Press the F5 key to run this macro. In the popping up Kutools for Excel dialog box, select the range that contains the names you want to flip, and click the OK button.

  4. In the next dialog box, please enter the existing separator used within names (such as a space in our case), and then click the OK button.

Result

Now the first names and last names in the selected range are flipped.


Above is all the relevant content related to flipping names in Excel. I hope you find the tutorial helpful. If you're looking to explore more Excel tips and tricks, please click here to access our extensive collection of over thousands of tutorials.

Comments (42)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
It worked for me.
Thank you!
This comment was minimized by the moderator on the site
hola, gracias por tus formulas, como puedo hacer si necesito reversar los nombres y luego los apellidos, por ejemplo tengo esto GRANDA VELASCO OMAR GERMANICO, y con la función revert no me funciona en orden me saca GERMANICO OMAR VELASCO GRANDA, me cambia el orden.
This comment was minimized by the moderator on the site
Hi there,

If you have additional middle names rather than just first and last names, please try the Reverse Text Order feature of Kutools for Excel.
https://www.extendoffice.com/images/stories/comments/ljy-picture/reverse-text.png

If you don't have Kutools for Excel installed in your computer, you can click here to download and try it for free for 30 days: Kutools for Excel. After the installation, you can find the feature on Kutools tab, in Editing group:
https://www.extendoffice.com/images/stories/comments/ljy-picture/reverse-text-2.png

Amanda
This comment was minimized by the moderator on the site
Thank you sooo very much! You made work easier for me!
This comment was minimized by the moderator on the site
=MID(A2&", "&A2,FIND(" ",A2)+1,LEN(A2)+1)

where the name is on the A2 cell
This comment was minimized by the moderator on the site
You saved my life!!! Thank you. None of the others worked. This is exactly what I needed.
This comment was minimized by the moderator on the site
=MID(O4&", "&O4,FIND(" ",O4)+1,LEN(O4)+1)
This comment was minimized by the moderator on the site
okay. can someone say this in english because I have no clue what you guys are talking about
This comment was minimized by the moderator on the site
Try this to add a comma: =MID(A2&", "&A2,FIND(" ",A2)+1,LEN(A2)+1)
This comment was minimized by the moderator on the site
THANK YOU literally I've been trying to figure out the comma thing for like an hour. Thank you!
This comment was minimized by the moderator on the site
What if I need to insert a comma between the last and first name?
This comment was minimized by the moderator on the site
What if there is a comma separating the 2 names? Is there a way to eliminate it? Now the names look like

Arthur Lange,

Eric Norris,

etc etc
This comment was minimized by the moderator on the site
Try this to remove a comma: =MID(A2&" "&A2,FIND(", ",A2)+1,LEN(A2)+1)
This comment was minimized by the moderator on the site
Have you tried Ctrl+F, Select Replace, Find "," and leave replace with blank.
This comment was minimized by the moderator on the site
When I try that, it pops up with this error message "There's a problem with this formula. Not trying to type a formula? When the first character is an equal (=) or minus (-) sign, Excel thinks it's a formula: you type: =1+1, cell shows: 2. To get around this, type an apostrophe (') first: you type: '=1+1, cell shows =1+1.
This comment was minimized by the moderator on the site
I think you are changing the formula. Try copying and pasting the values to a new cell then do the find/replace.
This comment was minimized by the moderator on the site
I want to know this too!
This comment was minimized by the moderator on the site
Correction. What if i have four names? What's the formula? Last name - first name - middle1 - middle2
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations