How to Split First and Last Name in Google Sheets (Easiest Way)

It’s no surprise that you often find yourself with a long list of full names that need to be split up. Whether it’s for event registrations, email lists, or company rosters, handling this task can be time-consuming. But fear not! In this article, we’ll walk you through the easiest ways to split first and last names in Google Sheets. You’ll discover how to use the built-in Text to Columns tool, the powerful SPLIT function, handy string functions, and even a popular add-on. Let’s get started!

Method 1: Split Names Using Text to Columns

The quickest way to split names in Google Sheets is by using the Text to Columns feature. This handy built-in tool separates your text based on a delimiter, which is something that separates two values. Luckily, since first and last names are separated by spaces, this makes it the perfect delimiter.

1. Select your full names in Google Sheets

2. Go to Data -> Split Text to Columns

3. Choose space as your delimiter next to your column

4. Voila! Your names are now split into separate columns in Google Sheets

Please note that using this method will place first names in the first column and remove the full names. It will also treat the headers in the same way. But don’t worry, there are other methods in Google Sheets that we’ll cover below.

Method 2: Separate First and Last Name using SPLIT Function

The SPLIT function in Google Sheets performs the same technique as the Text to Columns tool. It divides your text using a specified delimiter. Here’s how you can use it:

Syntax of the Function

The SPLIT function in Google Sheets has the following form:

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

Its main parts are:

• text: the full name you want to split
• delimiter: the separator you want to use (in our case, space)
• [split_by_each] and [remove_empty_text]: optional arguments that are not required for simple name division

To use this formula, follow this guide:

Using the SPLIT Function

1. Select the first cell next to the name you want to split.
2. Enter =SPLIT(cell_reference, "delimiter") in the cell and press enter.

Here, cell_reference is the name you want to split (e.g., cell A2), and our delimiter is a space. So, we use =SPLIT(A2," ").

3. Drag the fill handle across the cells below.

And just like that, you have your first and last names separated!

Method 3: Split Names Using Functions on Text Positions

Another powerful combination of functions in Google Sheets can help you sort your names. We’ll be utilizing the FIND, LEFT, RIGHT, and LEN functions.

Syntax of String Based Functions

Before we dive into using them all together, let’s take a quick look at each of them:

• LEFT and RIGHT are used in strings to return all characters to the left or right of a specified position within the string. They follow this syntax:

  • =LEFT(string, [number_of_characters])
  • =RIGHT(string, [number_of_characters])

  In these formulas, string refers to a set of words or characters (like the cells containing full names). number_of_characters represents the count of characters from the left or right ends of our string. However, using these two functions alone won’t be sufficient for dividing names because the number of characters in a first or last name varies. This is where the FIND and LEN functions come into play.

• FIND allows you to search for a particular character in the middle of a string and returns its position as a number. It follows this syntax:

  • =FIND(search_for, text_to_search,[starting_at])

  We’ll use FIND to locate the first space in the full names.

• LEN shows the total character count in a cell. Its syntax is:

  • =LEN(text)

Let’s now string these formulas together to split names in Google Sheets.

Using a String of Functions to Separate Names

1. To find the first name, enter the following formula in an adjacent cell:
   =LEFT(cell_reference, FIND(" ", cell_reference) - 1

   For cell_reference, use the cell containing the name. Subtracting 1 helps exclude the space, returning the characters to the left before the space (our first names).

2. To find the last name, enter the following formula in the cell for last names:
   =RIGHT(cell_reference, LEN(cell_reference) - FIND(" ", cell_reference))

   This formula subtracts the position of the first space from the total number of characters, giving you all the characters to the right of the string (our last names).

3. Copy the formulas down to complete.

Now you have your first and last names separated! But what about more complex cases with middle names, double-barreled surnames, and suffixes? Dealing with these types of names requires more than just splitting where spaces are found. Luckily, Google Sheets has an Add-On called Split Names that can handle these situations. Although it’s a paid subscription, there’s a free trial available. Here’s how to install and use it.

Method 4: Splitting Names in Google Sheets Using an Add-On

1. Go to Extensions -> Add-Ons -> Get Add-Ons and search for “Split Names” by Ablebits. Install the add-on.

2. Once installed, select the columns of names and navigate to Extensions -> Split Names -> Start.

3. In the pop-up, you can choose if your data has headers and select additional columns you want. Then, click “Split.”

4. The add-on will recognize the names and divide them into their respective columns.

And there you have it! These are the different ways to split full names into first and last names using Google Sheets. Try out the different methods to find the best one for your specific use case. Good luck!

For more tips and tricks on using Google Sheets and other useful tools, visit Mr Reviews.