How to Switch First and Last Name in Google Sheets with Comma (6 Easy Ways)

Switching the first and last name in Google Sheets is a common task when dealing with lists of names. However, doing it manually can be time-consuming and prone to errors.

Fortunately, there are several ways to do this quickly and easily in Google Sheets.

In this tutorial, we will explore 6 different ways to switch the first and last name in Google Sheets with a comma as a separator.

Switch First and Last Name Using Formula

The easiest way to switch the first and last name in Google Sheets is by using a combination of the SPLIT, INDEX, and SUBSTITUTE functions.

Let’s take the following full names dataset and see how we can switch the first and last names while separating them with a comma.

In the formula bar, type the following formula:

=SUBSTITUTE(A2,INDEX(SPLIT(A2," "),1)&" ","") & ", " & INDEX(SPLIT(A2," "),1)

This formula swaps the first and last names of the cell A2 and separates them with a comma.

Here’s a breakdown of the formula:

First, the SPLIT function is used to split the full name in the cell into separate words, with a space (” “) as the delimiter.

Next, the INDEX function is used to extract the first word (i.e., the first name) from the array of words obtained from the SPLIT function, using “1” as the index.

Then, the first name obtained in the previous step is concatenated with a space (” “) using the “&” operator. This creates a text string that consists of the first name followed by a space.

The SUBSTITUTE function is then used to replace occurrences of the first name followed by a space (as extracted in the previous step) with nothing (i.e., an empty string) from the original name. This effectively removes the first name from the full name, leaving only the last name.

A comma followed by a space (“, “) is added after the text obtained from the previous steps, creating the separator between the last name and the first name in the resulting text.

Again, the INDEX function is used with the SPLIT function to extract the first word (i.e., the first name) from the original text (Full name) in cell.

Finally, the resulting text is a combined string with the last name followed by the first name, separated by a comma and a space (“, “).

Switch First and Last Name Using REGEXREPLACE function

Another effective way to switch the first and last name in Google Sheets is by utilizing the inbuilt REGEXREPLACE Function.

This method involves using a regular expression to capture the first and last names as separate groups and then using these captured values to rearrange the names in the desired format (i.e., Last Name, First Name).

Let’s take the following full names dataset and see how we can switch the first and last name using REGEXREPLACE Function.

In the formula bar, type the following formula:

=REGEXREPLACE(A2, "^(\w+)(?:\s+(.+))?$", "$2, $1")

Working

The REGEXREPLACE function captures the first and last names as separate groups using a regular expression. It then rearranges the names with the captured values using the replacement pattern “$2, $1”, where $2 represents the last name and $1 represents the first name.

The formula consists of three parts:

• A2: the cell containing the name we want to switch.
• "^(\w+)(?:\s+(.+))?$": a regular expression that captures the first and last names as separate groups from the input string.
• "$2, $1": The replacement pattern that specifies how to rearrange the captured values. $2 represents the second capturing group (i.e., the last name), and $1 represents the first capturing group (i.e., the first name). It rearranges the names in the format “Last Name, First Name”.

Switch First and Last Name Using Split text to columns with JOIN function

In this method, we will use the Split Text to Columns functionality in Google Sheets to split the full name into first and last name, and then use the Join function to swap the first and last names with a comma in between.

Here’s how you can use the Split Text to Columns functionality to separate the first and last names into separate columns:

Before proceeding with the following steps, make sure to copy the full names from Column A to a new column to avoid losing the original data.

1. Select all the names in the new column that you created.

2. Click the Data menu and choose the “Split Text to Columns” option from the dropdown.

3. This will open this Separator window at the bottom right of the selected dataset.

4. From the Separator window, choose the space option as the delimiter.

The above steps will split the names in the selected cells into separate columns, you will have the first names in one column and the last names in another column.

Once you have separated the first and last names into separate columns, you can use the JOIN function to switch the first and last names with a comma in between.

To do this, enter the following formula in cell D1:

=JOIN(", ",C2,B2)

After pasting the formula, you will see that the first and last names have been switched with a comma in between, as desired.

Note: Make sure to adjust the cell references in the formula based on your specific dataset. For instance, if your first names are in column C and last names are in column D, you would need to modify the formula as follows: =JOIN(“, “, D1, C1)

Switch First and Last Name Using Smart Fill

If you’re looking for a quick and straightforward way to switch your first and last name, this method is perfect for you.

Let’s try using Smart Fill to swap the first and last names in the following names data set.

Here are the steps:

1. First, you need to create a new column next to the name’s data set.

2. In the first cell of the new column, enter the name in the format “Last Name, First Name” (e.g. “Brown, Jacob” in cell B2).

2. Begin typing in the second cell of the new column. As you do, Smart Fill will automatically recognize patterns and pop up the Smart Fill window along with a preview of the data to be auto-filled in the remaining cells.

3. Hit the “Enter” key to automatically fill the rest of the cells.

If the Smart Fill preview doesn’t show up even after you’ve entered 2 to 3 entries, you can use the Smart Fill shortcut.

Here’s how to do it:

1. Type in two entries and press Enter to select the cell below.

2. Then, use the keyboard shortcut Control+Shift+Y to trigger Smart Fill.

And there you have it! Using Smart Fill, you were able to quickly and efficiently swap the first and last names in your data set.

This is the formula that Smart Fill recommended for switching the first and last name.

=CONCATENATE(RIGHT(A2,LEN(A2) - (FIND(" ",A2))),", ",LEFT(A2,FIND(" ",A2) - 1))

Remember, Smart Fill works best when there is a clear pattern to the data you are entering, so be sure to format your data appropriately before using it.

Switch First and Last Name Using Find and Replace

You can use the Find and Replace feature in Google Sheets to easily switch the first and last name, with the comma in between.

This method also employs regular expressions, much like the second method.

Here’s how you can use the Find and Replace method to switch first and last names with a comma in between:

1. First, you need to copy and paste the full names from the original dataset into a new column. This is to ensure that you do not lose the original data while performing the following steps.

2. Select all the names in the new column that you created.

3. Go to the Edit menu and click on the Find and Replace option from the dropdown or Press Ctrl+H (Command+H on Mac).

4. In the “Find” field, type in the following regular expression: ^(\w+)(?:\s+(.+))?$. This regular expression captures the first word (i.e., the first name) and the last word (i.e., the last name) in each cell as separate capturing groups.

5. In the “Replace” field, type in the following replacement pattern: $2, $1. This replacement pattern swaps the captured first and last names and adds a comma and a space between them.

6. Check the corresponding checkbox of “Search using regular expressions” to enable regular expression matching.

7. Click on the “Replace All” button.

Once the Find and Replace operation is complete, you will see that the first and last names have been switched with a comma in between.

Switch First and Last Name Using Custom Apps Script Function

You can also take advantage of the Apps Script to create the Custom Function that can switch the first and last names with a comma.

I have created the following Apps Script code that creates a custom function that can accomplish this task:

function switchNames(names, delimiter = ",") {
  names = typeof names === "string" ? [[names]] : names;

  return names.map((getinside) => getinside.map((fullName) => {
      const nameArr = fullName.trim().split(' ');
      return `${nameArr.slice(1).join(" ")}${delimiter} ${nameArr.shift()}`;
  }));

}

To add this code to the Apps Script editor, follow these steps:

1. Click on the Extensions menu item and select the Apps Script option from the dropdown.

2. Copy and Paste the above code into the App Script code editor.
3. Click on the “Save” icon.

Now you can use the =switchNames() function just like any other built-in function in your spreadsheet.

=switchNames(names, delimiter(optional))

The above function takes two parameters:

• names: The cell or range of cells with the Full names you want to swap.
• delimiter (optional): The character or series of characters that will be added in between the first and last name after they have been switched. By default, a comma (“,”) is used as the delimiter, but you can specify a different delimiter if needed.

We’ll be using the same dataset to see how this method works.

In the formula bar, type the following formula:

=switchNames(A2:A10)

Once you enter the formula and press Enter, the first and last names will be successfully swapped, with the delimiter inserted between them.

I hope this tutorial has helped you understand how to switch the first and last name in Google Sheets.

Choose the method that suits your needs best.

Catch you guys in the next one!