Do you need to reverse the order of the first and last names in your dataset?

This post is going to show you how to easily switch first and last names in Google Sheets.

Often the first name will come before the last name in a dataset. In many situations, this arrangement may not be ideal as it won’t allow you to sort the data by the last name.

When the arrangement of the first and last names doesn’t work for what you need, you will have to rearrange them.

This post will show you several methods to switch the first and last names in Google Sheets.

Get your copy of the example workbook used in this post to follow along!

Switch First and Last Names with the LEFT and RIGHT Functions

This method uses a combination of some text functions. The functions used are the LEFT, RIGHT, LEN, and FIND functions. You will also use the ampersand & operator to join text.

LEFT & RIGHT Functions

= LEFT ( string, [number_of_characters] )

The LEFT function extracts a substring from the beginning of a specified text.

= RIGHT ( string, [number_of_characters] )

The RIGHT function extracts a substring from a specified text from the end of a string.

Both functions have the same arguments.

• string: is the text or cell containing the text from which you want to extract a substring.
• number_of_characters: references the number of characters you want to extract from the text. The argument isn’t needed for the function to work because it has a default value of 1. When omitted the LEFT and RIGHT functions will extract only one character from the specified text.

LEN Function

= LEN ( text )

The LEN function simply returns the number of characters in a cell, including delimiters and white space characters.

FIND Function

= FIND ( search_for, text_to_search, [starting_at] )

FIND identifies the position of a character within a specified string. It returns the position of the first occurrence of the searched character within a string.

This means that where a character appears more than once in a string, it returns the position of the first appearance of that character.

The FIND function uses three arguments.

• search_for: references the character to find within the specified string.
• text_to_search: references the string or cell containing the string where the search will occur.
• starting_at [optional]: allows you to specify the position in text_to_search which you want the search to start. It’s an optional argument that has a default value of 1. When omitted FIND will always search from the first character in text_to_search.

📝 Note: The FIND function is case-sensitive. It will return an error when you search for g in GOOGLE because the text is in uppercase, and the search key is in lowercase.

Switch First and Last Names

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

The above formula will switch the first and last names.

Although the formula looks long and complicated, you can understand it easily. Let’s break it down.

The first part of the syntax RIGHT(B3, LEN(B3) - FIND(" ", B3)) extracts the last name.

• LEN(B3) returns the number of characters in cell B3 which is 11 in this example.
• FIND(" ", B3) returns the position of the space character which is 6 in this example.

The difference between these two values is then passed as the number_of_characters argument in the RIGHT function so that it extracts the last name from cell B3. In this example, it’s the last 5 characters.

The second part of the syntax LEFT(B3, FIND(" ", B3) - 1) extracts the first name.

FIND(" ", B3) returns the position of the space character which is 6 in this example. But is reduced by 1 to eliminate the space character and get the position of the last character in the last name. This calculates to 5 in this example.

This gets returned for the number_of_characters argument for the LEFT function to extract the first 5 characters from cell B3.

The ampersand operator & in the formula is used to combine the last and first names with a comma in between.

Switch First and Last Names with the SPLIT and TEXTJOIN Functions

Just like the first method, this also involves using several functions to switch first and last names. The functions used are the TEXTJOIN, SORT, TRANSPOSE, SPLIT, SEQUENCE, and COUNTA functions.

TEXTJOIN Function

The TEXTJOIN function combines two or more strings using a specific delimiter.

= TEXTJOIN ( delimiter, ignore_empty, text1, [text2, …] )

TEXTJOIN uses three compulsory arguments to operate.

• delimiter: a character or reference to a valid character that the function will use to separate the strings when joining them.
• ignore_empty: is a Boolean argument. If you want to ignore empty cells in the output, set it to TRUE. Otherwise, you use FALSE. You can also use 0 or 1, where 0 = FALSE and 1 = TRUE.
• text1: this argument references the text you want to join. It can either be hardcoded into the formula or an array that contains string data. You can have additional text items as needed.

TRANSPOSE Function

TRANSPOSE converts rows to columns or columns to rows.

= TRANSPOSE ( array_or_range )

COUNTA Function

COUNTA returns the count cells that are not blank or empty. It will also include in the count cells that contain a white space character like spaces, tabs, or page breaks.

= COUNTA ( value1, [value2, …] )

The COUNTA function uses the value argument, and it can take an arbitrary number of values.

SORT Function

The SORT function rearranges values in ascending or descending order.

= SORT ( range, sort_column, is_ascending, [sort_column2, …], [is_ascending2, …] )

The SORT function has 3 arguments.

• range: references the array that you want to sort.
• sort_column: references the column you want to use to sort the selected range.
• is_ascending: takes a Boolean true or false argument. It determines whether to sort the range in ascending or descending order. To sort in descending order, set to FALSE. Set is_ascending to TRUE to sort in ascending order. You can have additional sort_column and is_ascending arguments to sort by more than one column.

SEQUENCE Function

SEQUENCE returns an array of consecutive numbers. This is a function you can use to generate a serial number column for your dataset. With sequence, you can easily create sequential numbers across an array.

= SEQUENCE ( rows, [columns], [start], [step] )

The SEQUENCE function has 4 arguments.

• rows: references the number of rows for which the function should generate sequential numbers.
• columns [optional]: references the number of columns for which numbers should be generated.
• start [optional]: the first value of the sequential numbers the function will generate.
• step [optional]: how much the sequential numbers should increase. It specifies the arithmetic progression of the numbers. By default, the SEQUENCE function generates numbers from 1 and increases by 1. But you can modify this.

= TEXTJOIN ( ", ", true, SORT ( TRANSPOSE ( SPLIT ( B3, " ", true, true ) ), SEQUENCE ( COUNTA ( SPLIT ( B3, " ", true, true ) ) ), false ) )

When you paste this syntax into the formula bar, it will switch the first and last names.

Separating the entry in this syntax into its component TEXTJOIN argument:

• delimiter = ", "
• ignore_empty_text = true
• text1 = SORT(TRANSPOSE(SPLIT(B3," ", true, true)), SEQUENCE(COUNTA(SPLIT(B3, " ", true, true))), false)

The SORT syntax in the text1 argument generates the texts that are joined. By separating the SORT function into parts, it becomes easy to understand how it works.

• range = TRANSPOSE(SPLIT(B3," ", true, true))

This syntax provides the SORT function with the array you want to sort. Starting from the inside out, the SPLIT syntax separates the text in cell B3 around the space character and places them in separate rows.

= SPLIT ( B3, " ", true, true )

Working only with the SPLIT syntax, the outcome will be as seen in the above image.

= TRANSPOSE ( SPLIT ( B3, " ", true, true ) )

The TRANSPOSE function changes the result of the SPLIT function from a one-row array to a one-column array.

This is then interpreted as the range C3:C4 in the range argument of the SORT function.

• sort_column = SEQUENCE(COUNTA(SPLIT(B3, " ", true, true)))

The sort_column argument uses the SEQUENCE formula.

When using the SEQUENCE function, you only need the row argument. The COUNTA and SPLIT syntax are used to derive the value for the row argument.

Starting with the SPLIT function, it separates the content in cell B3 into two rows as earlier. Then the COUNTA function counts the number of cells with a value.

= COUNTA ( SPLIT ( B3, " ", true, true ) )

Since the SPLIT function separates the text into two cells, the COUNTA returns a value of 2.

= SEQUENCE ( COUNTA ( SPLIT ( B3, " ", true, true ) ) )

This value then passes for the row argument in the SEQUENCE function, which will then return 1 & 2.

The value derived from this operation is interpreted as a range in the sort_column argument of the SORT function.

= SORT ( C3:C4, D3:D4, false )

The SORT function will then return the names in reverse order because it’s sorting based on the descending order of the SEQUENCE.

= TEXTJOIN ( ", ", true, SORT ( C3:C4, D3:D4, false ) )

The output from the SORT function now passes as the text1 argument in the TEXTJOIN function.

TEXTJOIN then combines both strings using the delimiter characters specified in the delimiter argument.

This results in the first and last names being switched!

Switch First and Last Names with the REGEXEXTRACT Functions

The REGEXEXTRACT function uses a regular expression to extract characters from a string.

A regular expression refers to special character combinations that are used to match text or string objects.

= REGEXREPLACE ( text, regular_expression )

REGEXEXTRACT has just two arguments.

• text: the string or cell with the string in which to perform the extraction.
• regular_expression: the regular expression syntax. REGEXEXRACT will return the first part of the text that matches this argument.

= REGEXEXTRACT ( B3, "\s(.*)") & ", " & REGEXEXTRACT ( B3, ".* " )

This syntax uses two REGEXEXTRACT functions to switch the first and last names.

The first REGEXEXTRACT function returns the first last name while the second REGEXEXTRACT function returns the last name.

The resulting output is separated using the space and comma delimiters. The syntax uses the ampersand operator to join the functions and the delimiter.

In the regular expression command, the dot . matches any character, except line breaks, that appears first in a string.

The asterisk * is a repeater command that matches any length of characters.

The \s command matches any white spaces. This includes spaces, tabs, line breaks, etc.

The combination of these commands ensures that REGEXEXTRACT returns the desired output.

Switch First and Last Names with Split Text to Columns

It can be easier to use the Split text to columns feature to switch first and last names if it’s a one-off operation.

You can separate the first and last names into separate columns with Text to columns, then you join the output using the ampersand operator, CONCATENATE, or TEXTJOIN functions.

Follow these steps to separate the first and last names.

1. Select the range that contains the text you want to split

2. Go to the Data menu.
3. Select Split text to columns.

A small Separator menu appears on the spreadsheet. Split text to columns tries to automatically detect the delimiter around which to split the text. In this case, it can’t so the text remains unsplit.

4. Click on the Separator box.
5. Select Space from the options.

This will split the text into separate columns.

= C3 & ", " & B3

Create a new column, then join the Last column and the First columns using the above syntax.

Alternatively, you can join the names using CONCATENATE or TEXTJOIN.

Switch First and Last Names with Smart Fill

The Smart fill feature is one of the simplest ways you can switch first and last names. This is because, with the Smart fill feature, you only have to start typing the names.

After filling two or three cells, Smart fill recognizes the pattern using its AI capabilities. It then uses this pattern to predict and provide a preview of the results for the remaining cells. It also returns the formula it uses to perform the task.

All of these are suggested, and you can choose to accept or reject the Smart fill’s suggestion.

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

In this example, it automatically creates the above formula to switch the first and last names.

Switch First and Last Names with a Custom Apps Script Function

So far you have seen a number of formulas used to switch names.

But this can be simplified if you can create a custom function that switches names. This way you don’t need to create long formulas.

To create a custom function, go to the Extensions menu and select Apps script to open the editor window.

function SWITCHNAMES(names) {
  var selectedColumns = names[0].length;
  var selectedRows = names.length;
  var nameArray = [];

  if (selectedColumns > 1) {
    return [["Must reference a single column!"]];
  };

  for (i = 0; i < selectedRows; i++) {
    for (j = 0; j < selectedColumns; j++) {
      var nameSplit = names[i][j].split(' ');
      var firstName = nameSplit[0];
      var lastName = nameSplit[1];
      var namesJoin = lastName.concat(", ", firstName)
      nameArray.push(namesJoin);
    }
  }
  return nameArray
};

Copy and paste the above code into the editor. Save the script and click on Run. Grant the necessary permissions and reload your spreadsheet.

On reloading the spreadsheet, the syntax creates the new SWITCHNAMES custom function. The function switches the names while also including the comma delimiter.

= SWITCHNAMES ( B3:B12 )

You only need to provide the range containing the names you want to switch, and the single function will switch the names!

Conclusions

Often full names will appear as the first name followed by the last name. But you can easily rearrange the order to suit your needs when necessary.

For a one-off situation switching the first and last names with the Split text to columns might be the way to go!

But for more dynamic solutions a formula is best. User-friendly tools like Smart fill will even create the formula for you based on a few examples provided.

If you are constantly performing this task, you can benefit from a custom app script function that will make the process more simple!

Have you ever needed to switch the first and last names in a cell? How do you prefer to do it? Let me know in the comments below!