Do you need to switch the first and last names with a comma?

One of the issues in dealing with contact data is the format of their full name. For contacts whose full name appears as First Last, you would likely rather format it as Last, First.

Doing so usually leads to easier sorting and filtering in your list. Also, last names are generally more unique than first names which makes any lookups more reliable.

This post explores several different ways to reverse the first and last names in Excel.

Switch First and Last Names with Text to Columns

One way to switch first and last names is through a feature called Text to Columns.

This feature can separate first and last names into their own cells, where they can be re-combined into the new format using a formula.

To get started, select the cells containing your contacts.

Go to the Data ribbon tab and click Text to Columns.

Ensure Delimited is chosen as your Original data type. This is because the space character acts as a separator between the first and last name.

Click the Next button when ready.

Out of the above Delimiters, check the Space option as your delimiter and clear all the other Delimiters checkboxes.

The Data preview shows how Excel will separate the names.

Click the Next button.

Select the Destination textbox, then select one cell that will be the starting point of your separated names.

In the above example, the first name of your first contact will go into the cell $C$3 while the last name will go into the adjacent right cell. The rest of your contacts will get separated similarly going downward.

Click the Finish button.

As you can see above, the first and last names have been separated into their own columns.

= D3 & ", " & C3

The next step is to re-combine the columns to switch the names.

In the row of your first contact, select an empty cell and paste the above formula into the formula bar.

The ampersand (&) joins the last name from cell D3 with the first name from cell C3 and separates them with a comma (,) in between.

Press Enter to calculate the formula.

To apply the formula to the other contacts, hover over the lower-right corner of the formula’s cell until your cursor becomes a plus (+), then left-click and hold your mouse button while dragging downward.

Your contact names are now switched!

Switch First and Last Names with Text Formula

If you’d rather use a formula-only solution to switch names, there is a way to do it.

Such a formula will append results from the FIND, LEN, RIGHT and LEFT functions to format each name into a new cell.

FIND ( " " , B3 )

Given a full name in cell B3, the above FIND function returns the position of the space character, starting from the left.

LEN ( B3 ) - FIND ( " " , B3 )

To calculate the position of the space character starting from the right, the FIND result is subtracted from the length of the full name.

RIGHT ( B3 , LEN ( B3 ) - FIND ( " " , B3 ) )

The last name is extracted using the above RIGHT function.

LEFT ( B4 , FIND ( " " , B4 ) - 1 )

Extracting the first name is similar, as shown above. But 1 is subtracted from the FIND result so that the space character gets excluded.

= RIGHT ( B3, LEN ( B3 ) - FIND ( " ", B3 ) ) & ", " & LEFT ( B3 , FIND ( " " , B3 ) - 1 ) 

Putting all of this together, paste the above formula into the empty cell to the right of your first contact. The formula joins together the last and first name, with a comma (,) in between.

Press Enter to trigger the formula calculation.

To apply the formula to your other contacts, hover over the lower-right corner of the formula’s cell until your cursor becomes a plus (+), then left-click and hold your mouse button while dragging downward.

Voila! Your name formatting is complete!

Switch First and Last Names with TEXTSPLIT Function

TEXTSPLIT is a new function introduced in Excel 365 beta version 2203.

The TEXTSPLIT function separates a cell value into multiple cell values. It gives you the functionality of the Text to Columns feature but through a formula.

TEXTSPLIT ( B3 , " " )

The above TEXTSPLIT finds the space character within the contact in cell B3. All characters to the left of the space are extracted as one item, as are all characters to the right.

The result is a two-item array holding the first name followed by the last name.

SEQUENCE ( COUNTA ( TEXTSPLIT ( B3 , " " ) ) )

The COUNTA function counts the number of array items, which for most names will be two.

The SEQUENCE function uses that count to create a two-item array of sequenced numbers. This is what will be used to reverse the order of the names.

SORTBY ( TRANSPOSE ( TEXTSPLIT ( B3 , " " ) ) , SEQUENCE ( COUNTA ( TEXTSPLIT ( B3 , " " ) ) ) , -1 )

Both arrays are supplied to the SORTBY function which rearranges the names as a result of their corresponding numbers.

The -1 enforces the sort as reversed, effectively switching the names!

TRANSPOSE is required because SORTBY expects the names array to be vertically populated like the numbers array.

= TEXTJOIN ( ", " , TRUE , SORTBY ( TRANSPOSE ( TEXTSPLIT ( B3 , " " ) ) , SEQUENCE ( COUNTA ( TEXTSPLIT ( B3 , " " ) ) ) , -1 ) )

The final version of the formula is above. Paste this version into your formula bar.

The above TEXTJOIN function converts the array of switched names into a single full name, including the embedded comma (,).

The only step left is to copy the formula to the other contacts. Hover over the lower-right corner of the formula’s cell until your cursor becomes a plus (+), then left-click and hold your mouse button while dragging downward.

You now have all your contacts’ names flipped with a comma in between!

Switch First and Last Names with Flash Fill

The Flash Fill feature is a great way to switch first and last names without having to write complex formulas.

By providing some examples of switched names, Flash Fill can learn from your examples and automatically switch your remaining names!

Start a Flash Fill by first selecting the empty cell to the right of your first contact. In that cell, type the contact’s name with the first and last names switched. Then press Enter.

Ensure that the active cell is still within the column of your example.

Go to the Data ribbon tab and click Flash Fill, or simply press Ctrl + E.

Magically, Flash Fill recognizes that you want first and last names switched and will offer you a preview of the results!

To accept the results, click the Flash Fill Options menu at the lower-right corner of your cell, then click Accept suggestions.

You are all done and the names are all reversed!

Switch First and Last Names with Power Query

Power Query is a feature of Excel that allows you to perform complex transformations on your data.

To switch first and last names, you can give Power Query some examples and once the pattern is recognized, Power Query will finish the task for you and create the formulas required for the transform!

To launch Power Query, first select your column of contacts, including your header.

Go to the Data ribbon tab and click From Table/Range.

From the Power Query Editor window, go to the Add Column tab, then click Column From Examples.

You need to provide enough examples for Power Query to recognize the switch pattern.

Type your first example in the first row of the right column. For example, if your first contact is “Trent Buckston”, type “Buckston, Trent”, as shown above.

Press Enter after typing.

📝 Note: You don’t need to provide examples in order starting with the first row. You can the examples in any rows.

Type a second example in the second row to further help Power Query.

Again, press Enter after typing.

Notice above how Power Query uses your examples to guess the remaining rows. The results are not exactly what you want, so you need to provide additional examples.

After you provide your third example, you will finally see the desired results!

Select the Custom column header and type a descriptive name such as Switched, then press Enter and click the OK button.

Note above that the original Full Name column still appears. Because you already have the Full Name column on your sheet, you can remove that column.

Select the column and go to the Home tab then click on the Remove Columns command.

Place the Switched column onto your sheet by clicking the Close & Load dropdown, then Close & Load To.

From the Import Data window, select the Table option and choose Existing worksheet. Highlight its textbox contents and select an empty cell on your sheet. This cell will become the first row of your results.

When done, click the OK button.

As you can see above, your column of switched names now appears on your sheet!

Switch First and Last Names with Power Pivot

Power Pivot is an add-in for Excel that adds business intelligence to large amounts of data.

With Power Pivot, you can add a calculated column of switched names, then display that column with a pivot table.

By default, Power Pivot is disabled in Excel. But you can enable it to add the Power Pivot tab to your Excel ribbon.

Select your contact data, go to the Power Pivot ribbon tab and click Add to Data Model.

Your contact data now appears as a table column in Power Pivot. It is helpful to give this table a more descriptive name by double-clicking the bottom tab and renaming it Names.

Press Enter when done.

= RIGHT( Names[Full Name] , LEN( Names[Full Name] ) - FIND( " " , Names[Full Name] ) ) & ", " & LEFT( Names[Full Name] , FIND( " " , Names[Full Name] ) - 1 )

To create the column of switched names, select the column labeled Add column.

Paste the above formula into the formula bar. This formula performs the same actions as the text formula solution seen earlier in this post.

The key difference is that Power Pivot uses a formula language called DAX to reference an entire column at once!

For example, the formula references the Full Name column of the Names table using the Names[Full Name] syntax.

After you press Enter, all your switched names appear in the new column!

Give your new column a more descriptive name by double-clicking the column header and typing Switched.

Press Enter when done.

You are now ready to display the new column on your sheet. Go to the Home tab and click PivotTable.

Choose a destination for your Pivot Table. In the above example, a new worksheet will be created.

Click the OK button when ready.

In the PivotTable Fields pane, drill into the Names table and check the Switched field, so that the field appears in the Rows area.

Your switched names are now on your sheet inside a pivot table!

Switch First and Last Names with VBA

Excel’s VBA programming language offers you a highly automated, sophisticated solution to switching your names.

Through VBA, you can create code that iterates over each contact and then separates and re-joins their name in reverse order before outputting it to a new column.

To add such code to your workbook, open the VBA Editor by pressing Alt + F11.

It is a good idea to keep your custom code separate from the workbook itself, so create a module by selecting the Insert menu, then selecting Module.

Sub SplitNames()
Dim rng As Range
Dim arrNames() As String
Dim colCount As Integer

colCount = Selection.Columns.Count
If colCount <> 1 Then
    MsgBox ("Select a single column!")
    End
End If

For Each rng In Selection
    arrNames = Split(rng.Value, " ")
    rng.Offset(0, 1).Value = arrNames(1) & ", " & arrNames(0)
Next rng
End Sub

Double-click the new module to open it, then paste the above VBA code into the module.

At run-time, the above code first ensures that you’ve selected only one column of contacts on your sheet. Any other selection causes execution to end with a notification.

The code loops through each selected cell and separates the first and last names. The separation occurs at the position of the space character via the Split function.

Both names are placed in an array called arrNames. The array elements are re-joined in reverse order using the ampersand (&) and placed in the adjacent cell represented by rng.Offset(0, 1).

VBA code such as this is often referred to as a macro when it can be run from your sheet.

To run this macro, go back to your sheet and ensure you’ve selected your column of contacts.

Select the View ribbon tab and click Macros.

From the Macro dialog, select your SplitNames macro and click the Run button.

When the run finishes, you can see the adjacent column now contains your contacts with reversed names!

Switch First and Last Names with Office Scripts

With the advent of Microsoft 365 for the web, a new scripting language called Office Scripts is available to business plan users of Excel.

You can take advantage of Office Scripts to automate tasks like the switching of first and last names.

To start your own script, open your workbook from your web browser.

Select the Automate ribbon tab, then click New Script.

If you don’t see the Automate tab, check your Microsoft 365 license to ensure that you have the required business plan to access Office Scripts and that it’s been enabled in your admin settings.

function main(workbook: ExcelScript.Workbook) {

  //getselected range
  let rng = workbook.getSelectedRange();
  let rows = rng.getRowCount();
  let cols = rng.getColumnCount();

  if (cols != 1) {
    return;
  };

  //loop through selected cells
  for (let i = 0; i < rows; i++) {
    for (let j = 0; j < cols; j++) {
      //split name based on space
      let txtName = rng.getCell(i, j).getValue().toString();
      let arrName = txtName.split(" ");
      //enter names in adjacent cells
      rng.getCell(i, j + 1).setValue(arrName[1] + ", " + arrName[0]);
    };
  };
};

In the Code Editor pane that appears, replace its entire contents with the above script.

📝 Note: You always need a main function in your script because that is the starting point of every script.

This script first ensures that you have only one column of contacts selected, otherwise the script terminates.

The script loops through each contact. The first and last names get extracted by finding the position of the space character.

The two names get re-combined in reverse order with a comma (,) in between. The result gets placed into the adjacent cell before processing the next contact.

Before running your script, go to your sheet and select your column of contacts.

Click the Run button in the Code Editor pane to execute the code.

When it finishes, you can see the adjacent column now has the contacts with their names switched!

Conclusions

In this post, you’ve seen many ways to switch first and last names in Excel.

The Text to Columns feature offers a clean, interactive experience, whereas the text formula method benefits from fewer steps and the results will dynamically update if the source names change.

The formula involving TEXTSPLIT exposes you to the benefits of Excel’s dynamic array functions. But keep in mind not all users will have this function.

The Flash Fill feature is great for patterned tasks like switching names but may fall short when your data is inconsistently formatted.

For larger data sources, you could choose Power Query or Power Pivot. Power Query requires less formula knowledge with the powerful column by example feature.

Otherwise, you have VBA and Office Scripts as flexible coding options depending on if you work with the desktop app or the online app.

Have you needed to switch names in Excel? How did you get it done? Let me know in the comments.