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:
- 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".
- 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.
- 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.
- Přejděte na Kutools tab on the Excel ribbon and select AI asistent k otevření Kutools AI asistent rozhraní.
- Select the names you want to flip.
- 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".
- Stisknout vstoupit nebo zasáhnout Poslat tlačítko .
- 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.
- 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.
- 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:
- 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. - Drag the fill handle at the bottom right corner of cell B2 downwards to apply the formula to all relevant cells.
- 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.
- Select the range that contains the names you want to reverse.
- Přejděte na Data a klikněte na Z tabulky/rozsahu v Získejte a transformujte data skupina.
- If your data isn't in a table format, Excel will prompt you to create one. Please click OK.
- 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.
- 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.
- 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.
- 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.
- 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.
- 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.
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.
- Podržte stisknuté tlačítko Alt + F11 klávesy v aplikaci Excel k otevření Microsoft Visual Basic pro aplikace okno.
- 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.
- 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 .
- 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ů.
Nejlepší kancelářské nástroje produktivity
Kutools pro Excel - pomůže vám vyniknout před davem
Kutools pro Excel se může pochlubit více než 300 funkcemi, Zajištění toho, že to, co potřebujete, je jen jedno kliknutí...
Záložka Office - Povolte čtení a úpravy na záložkách v Microsoft Office (včetně Excelu)
- Jednu sekundu přepnete mezi desítkami otevřených dokumentů!
- Snižte stovky kliknutí myší každý den, sbohem s myší rukou.
- Zvyšuje vaši produktivitu o 50% při prohlížení a úpravách více dokumentů.
- Přináší efektivní karty do Office (včetně Excelu), stejně jako Chrome, Edge a Firefox.