Перейти до основного матеріалу

Змініть імена та прізвища в Excel: легкий повний посібник

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 Флеш-заповнення 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: "Прізвище Ім'я".
  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. прес Що натомість? Створіть віртуальну версію себе у to confirm the Flash Fill suggestions.

    Порада: As an alternative to steps 2 and 3, you can press Ctrl + E on your keyboard, or go to the дані tab on the Ribbon and click on the Флеш-заповнення button to fill the cells below with the pattern you provided in B2.

Примітки:

  • 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 рекомендується.

Switch first and last names using AI

Kutools для Excel features an AI-powered assistant, А. І. помічник, 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:

Примітка: 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.

Завантажити Kutools для Excel to access the feature and unlock your 30-day free trial!
  1. перейдіть до Кутулс tab on the Excel ribbon and select А. І. помічник відкрити Kutools AI Aide Інтерфейс
  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. прес Що натомість? Створіть віртуальну версію себе у або натисніть на Відправити button .

  5. Kutools AI Aide promptly processes your command, presenting a solution. Simply click the Виконати button to apply the rearrangement across your selected data.

Результат

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. Просто натисніть на Незадоволений кнопки. 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.

Примітка: Хочете отримати доступ до Kutools AI Aide? Download Kutools для Excel зараз! Окрім цього, Kutools може похвалитися безліччю понад 300 інших функцій і пропонує 30-денну безкоштовну пробну версію. Не чекайте, спробуйте вже сьогодні!


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, починаючи з 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)
    Порада: 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.

Порада:
  • 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!

З Зворотний порядок тексту 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 для Excel: Integrating AI 🤖, 300+ handy Excel functions at your fingertips. Спробуйте їх усі в 30-денній безкоштовній пробній версії без обмежень функцій зараз!


Switch first and last names using Power Query

використання 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:

Примітка: Виберіть Power Query метод exclusively in specific scenarios: якщо 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 Флеш-заповнення, AIабо формули (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. перейдіть до дані вкладка і натисніть кнопку З таблиці/діапазону в Отримати та трансформувати дані група.
  3. If your data isn't in a table format, Excel will prompt you to create one. Please click OK.

  4. У Power Query редактор that opens up, right-click on the header of the name column and then select Розділена колонка > За роздільником.

  5. Choose the delimiter separates your names (typically a space) and opt to split at each occurrence of the delimiter, потім натисніть кнопку 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 Об’єднати стовпці.

  8. У Об’єднати стовпці dialog, from the сепаратор в розкривному списку виберіть виготовлений на замовлення 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 Закрити та завантажити кнопка в Power Query редактор щоб застосувати ваші зміни.

Результат

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

Порада: 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 оновлення.


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.

Примітка: 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. Утримуйте клавішу Alt + F11 клавіші в Excel, щоб відкрити Microsoft Visual Basic для додатків вікна.
  2. Натисніть Insert > Модуліі вставте наступний макрос у Модулі вікна.
    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

    Примітка: 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 "" з "" will separate the names with a space instead of a comma.

  3. Натисніть F5 ключ для запуску цього макросу. У спливаючому Kutools для 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 кнопки.

Результат

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, Будь ласка, натисніть тут щоб отримати доступ до нашої великої колекції з понад тисячі посібників.