Přejít k hlavnímu obsahu

Přepínání křestních jmen a příjmení v Excelu: Jednoduchý kompletní průvodce

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 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: "Příjmení jméno".
  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. Stisknout vstoupit 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.

Poznámky:

  • 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 popsané v další části.
  • 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 je doporučeno.

Switch first and last names using AI

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

Poznámka: 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.

Ke stažení Kutools pro Excel to access the feature and unlock your 30-day free trial!
  1. Přejděte na Kutools tab on the Excel ribbon and select AI asistent k otevření Kutools AI asistent rozhraní.
  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. Stisknout vstoupit nebo zasáhnout Poslat tlačítko .

  5. Kutools AI asistent promptly processes your command, presenting a solution. Simply click the Provést button to apply the rearrangement across your selected data.

Výsledek

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. Jednoduše klikněte na tlačítko Nespokojený . Kutools AI asistent 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.

Poznámka: Chcete získat přístup k Kutools AI asistent? Stažení Kutools pro Excel Nyní! Kromě toho se Kutools může pochlubit nesčetným množstvím více než 300 dalších funkcí a nabízí 30denní bezplatnou zkušební verzi. Nečekejte a vyzkoušejte to ještě dnes!


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, začínající od 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.

Tip:
  • 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!

S Opačné pořadí textu 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 pro Excel: Integrating AI 🤖, 300+ handy Excel functions at your fingertips. Vyzkoušejte je všechny v 30denní bezplatné zkušební verzi bez omezení funkcí nyní!


Switch first and last names using Power Query

Použití 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:

Poznámka: Rozhodněte se pro Power Query metoda exclusively in specific scenarios: pokud 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, AInebo vzorce (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. Přejděte na Data a klikněte na Z tabulky/rozsahu v Získejte a transformujte data skupina.
  3. If your data isn't in a table format, Excel will prompt you to create one. Please click OK.

  4. v Power Query editor that opens up, right-click on the header of the name column and then select Rozdělit sloupec > Oddělovačem.

  5. Choose the delimiter separates your names (typically a space) and opt to split at each occurrence of the delimiter, poté klepněte na tlačítko 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 Sloučit sloupce.

  8. v Sloučit sloupce dialog, from the oddělovač z rozevíracího seznamu vyberte Zvyk option and input a comma followed by a space (, ) for the separator. If needed, uveďte jméno for the merged column, then click OK.

  9. Once satisfied with the preview, click the Zavřít a načíst tlačítko v Power Query editor použít vaše změny.

Výsledek

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 Obnovit.


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.

Poznámka: 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. Podržte stisknuté tlačítko Alt + F11 klávesy v aplikaci Excel k otevření Microsoft Visual Basic pro aplikace okno.
  2. klikněte Vložit > Modula vložte následující makro do Modul okno.
    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

    Poznámka: 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 "" s "" will separate the names with a space instead of a comma.

  3. Stiskněte F5 klíč ke spuštění tohoto makra. Ve vyskakovacím okně Kutools pro Excel dialog box, select the range that contains the names you want to flip, and click the OK .

  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 .

Výsledek

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, prosím klikněte zde pro přístup k naší rozsáhlé sbírce více než tisíců výukových programů.

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