Do you need to separate the first and last names in a list? This post is going to show you all the ways to split first names and last names into different cells in Google Sheets!

Datasets that have a list of people’s names, the first and last names usually appear together in one cell and are separated by a space character.

There’s nothing wrong with this. However, it can be more useful to get the first and last names into separate columns. This will make any further analysis easier as you can then sort and filter your data based on the last name.

Thankfully there are many easy ways to separate names into first and last name columns in Google sheets.

Get your copy of the example workbook in this post and follow along to find out how to split first and last names

Identifying the Delimiter

Before discussing the methods for separating first and last names, it’s important to understand the role of a delimiter.

Delimiters are characters used to separate one block of text from another.

The most common character used as a delimiter is the space which is used to separate words. But other characters such as the comma, semi-colon, hyphen, or underscore are used frequently.

In this sample data, the Name column contains first and last names that are delimited or separated with the space character.

The delimiter character is consistent across the entire column which is essential because it makes it easy to separate your data based on the delimiter.

Before you begin the process of separating a list of names, scan through the data to be sure it uses a consistent format with spaces.

Separate First and Last Names with the LEFT and RIGHT Functions

The first method involves using the LEFT, RIGHT, LEN, and FIND text functions. Using these functions together will separate the names into first and last names.

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 also extracts a substring from a specified text, but it does so from the end of the specified text.

Both the LEFT and RIGHT functions have the same arguments which are string and number_of_characters.

The string argument refers to the text or cell containing the text from which you want to extract a substring.

The number_of_characters is where you specify the number of characters to extract from the specified text. This is an optional argument and when omitted, the LEFT function will extract only one character from the beginning of the string and the RIGHT function will extract one character from the end of the specified text.

LEN Function

= LEN ( text )

The LEN function simply returns the count of all characters from the text. This includes any delimiters.

FIND Function

= FIND ( search_for, text_to_search, [starting_at] )

FIND is useful for locating a specific character within a string.

It returns the position of the first occurrence of the searched character within a string. If you’re looking for o in Google, FIND will return 2.

The FIND function uses three arguments.

• search_for is the character to find within the specified string.
• text_to_search is the string or cell containing the string where the search will occur.
• starting_at allows you to specify the character within the text_to_search argument where you want the search to start. It’s an optional argument that has a default value of 1. This means FIND will always start searching from the first character in text_to_search by default.

💡 Tip: The FIND function is case-sensitive! That means searching for g in GOOGLE or searching for G in google will both return an error.

Get the First Name with the LEFT Function

= LEFT ( B3, FIND ( " ", B3 ) - 1 )

When you copy and paste this syntax into your formula bar, it will extract the first name into the new cell.

The FIND function returns the position of the space character in the string. One is then subtracted from this number to return the number of characters in the first name.

This is necessary to prevent the LEFT function from returning the space character as a part of the final output.

The LEFT function uses this number as the number_of_characters argument to extract the first name.

Get the Last Name with the RIGHT Function

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

The process for returning the last name is a bit more complex.

Here the LEN function is introduced to the syntax. The LEN function returns the total number of characters in the string.

The FIND function is used to return the position of the space character. The value of the LEN function is then reduced by this amount to return the number of characters in the last name.

The difference between the total character length and the position of the space character results in the length of the last name. This is passed as the number_of_characters argument enabling the RIGHT function to return the characters from the end of the string which makes up the last name.

The value the LEN and FIND functions return will differ for each name because of the difference in length of the characters and the position of the space character. But the syntax will always return the accurate last name because the delimiter used is the same and it correctly separates the names into first and last.

Separate First and Last Names with the SPLIT Function

Using the LEFT and RIGHT functions to extract first and last names can be a little complicated.

You don’t have to use it though because using the SPLIT function is a much more simple approach. With SPLIT, you only need to provide the string, and the delimiter separating them.

= SPLIT ( text, delimiter, [split_by_each], [remove_empty_text] )

The SPLIT function uses four arguments to separate characters. With SPLIT, you only need to use the text and delimiter arguments.

The split_by_each argument makes it possible to split strings with multiple delimiters and it is true by default.

The remove_empty_cells argument determines whether the output should include empty cells or not.

= SPLIT ( B3, " ")

This simple syntax will split the first and last names into separate columns. You can then copy the formula into other cells to complete the separation across your list.

SPLIT doesn’t return the delimiter in the output which is a possibility when using the LEFT and RIGHT functions.

📝 Note: Be sure to leave enough empty cells to the right for the output because it separates the fragments into new cells in the row.

Separate First and Last Names with the REGEXEXTRACT Function

The REGEXEXTRACT function can be used to extract either the first or last name based on the space that separates them.

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

= REGEXREPLACE ( text, regular_expression )

REGEXEXTRACT has two arguments.

• text is the text or cell with the text from which you want to extract.
• regular_expression is the regular expression syntax. REGEXEXRACT will return the first part of the text that matches this argument.

Extract the First Name with the REGEXEXTRACT Function

= REGEXEXTRACT ( B3, ".* " )

This formula will extract the first name when you copy and paste it into the formula bar.

Understanding why the formula works require some knowledge of regular expression syntax. The regular expression ".* " used in the formula contains a period, an asterisk, and a space character.

The period notation matches any single character except line breaks.

The asterisk is a repeater character so .* will match any length of characters.

Adding a space to the end ensures only characters from the beginning of the string up to the space are matched and extracted.

Extract the Last Name with the REGEXEXTRACT Function

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

This is the syntax needed for extracting the last name.

The \s notation will match any whitespace character such as spaces, tabs, line breaks, etc.

Its use in this syntax ensures that the formula starts the match from where the space is located in the name and extracts all characters that appear afterward.

📝 Note: The syntax for extracting the first name could also be written as =REGEXEXTRACT(B3, "(.*)\s")

Separate First and Last Names with Split Text to Columns

The methods for extracting the first and last names discussed so far have involved the use of formulas. This is one of the methods that doesn’t require the use of any formulas.

Split text to columns is a spreadsheet feature that allows you to split the text into separate columns. It’s a straightforward process and one easy way to split cells into multiple columns.

Follow these steps to use the Split text to columns feature,

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

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

A small Separator menu appears on the spreadsheet after selecting the Split text to columns option.

By default, Split text into columns tries to automatically detect the delimiter around which to split the text. In this case, it can’t so the column remains unchanged.

4. Click on the Separator box and select Space from the options.

As soon as you do this, the text is split into separate columns.

You can change the column headers for the first and last name columns.

Separate First and Last Names with Smart Fill

Smart fill is another solution that doesn’t require the need to write formulas.

Smart fill is a little better than using the Split text to columns feature because it automatically generates a formula to get its results.

In situations where you have some complex splitting tasks to perform, Smart fill can be an absolute lifesaver.

Check out this post for more on how to use Smart fill in Google Sheets.

To use Smart fill to split the first name, all you just do is start typing an example of the results you want.

After filling two or three cells, Smart fill recognizes the pattern using its AI capabilities. Then it uses this pattern to predict and provide a preview of the results for the remaining cells.

= LEFT ( B3, FIND ( " ", B3 ) - 1 )

It also generates the above formula that you can use to perform the task!

All of these are suggested and you can choose to either accept or reject the suggested results.

You can extract the last name the same way as well. Just type out a few examples.

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

You will notice that the suggested formula is the same as those described in the first method with the LEFT and RIGHT functions.

⚠️ Warning: Smart fill will also consider information on other sheets, so it might suggest a VLOOKUP formula if it finds these names in other sheets.

Separate First and Last Names with an Apps Script

All the formulas and features discussed so far need you to specify the delimiter character for the split to return the appropriate result.

With an apps script, you can simplify the formula by creating a custom function that can be used for the specific case of splitting names.

You can check out this post for more details on Google Sheets Apps Scripts.

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

function SPLITNAMES(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++) {
      nameArray.push(names[i][j].split(' '));
    }
  }
  return nameArray
};

In the editor window, copy and paste this syntax. Save the script and click on Run. Grant the necessary permissions and reload your spreadsheet.

On reloading the spreadsheet, the syntax creates the new SPLITNAMES custom function. The syntax also specifies the delimiter around which the text should be separated.

= SPLITNAMES ( B3:B12 )

The above formula will split the first and last names.

You only need to provide the range containing the names you want to separate. The single formula will split the entire range of names referenced.

Conclusions

Your dataset might include full names in a single column. But by splitting the first and last names into separate cells, you can use each component for any further analysis.

There are several formula-based solutions to isolate both the first and last names. You can use combinations of various text functions, the SPLIT function, or the REGEXEXTRACT function to separate the first and last names.

The Split text to columns feature is a much simpler alternative, and the Smart fill feature is perfect for when you want to use a formula without having to create it yourself.

You can also create a custom function for the sole purpose of splitting names. This simplifies the formula and can handle an entire column of names with one formula!

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