If you have a list of full names in a single column in Google Sheets and you need to separate them into first names and last names, this tutorial is for you. Google Sheets offers several built-in functions that make splitting names or any other data a breeze. Today, we will be using the SPLIT function.
Understanding the SPLIT function
The SPLIT function divides text around a specified character or string, and puts each fragment into a separate cell in the row. The syntax for the function is as follows:
=SPLIT(text, delimiter, [split_by_each], [remove_empty_text])
Here, ‘text’ is the text to divide, ‘delimiter’ is the character or characters to split by, ‘split_by_each’ is true or false depending on whether or not to divide the text around each character in the delimiter, and ‘remove_empty_text’ is true or false depending on whether or not to remove empty text fragments.
Steps to Separate Names in Google Sheets
Now, let’s use the SPLIT function to separate full names into first names and last names.
Note: Before you begin, it’s a good idea to make a copy of your data, or to work on a copy of your data. The SPLIT function will overwrite cells to the right of the cell containing the function.
Step 1: Prepare Your Data
Assuming your full names are in column A, insert two new columns (B and C) to your right where the separated first names and last names will appear.
Step 2: Use the SPLIT Function
In cell B2, type the following formula:
=SPLIT(A2, " ")
Here, “A2″ is the cell containing the full name, and ” ” (a space) is the delimiter used to separate the name. This formula assumes that your names are separated by a single space. If your names are separated by a different character, replace the space between the quotation marks with that character.
Step 3: Apply the Formula to All Cells
Once the formula is working correctly for the first name, click on cell B2 again. Then, click on the small blue square in the bottom-right corner of the cell and drag it down to apply the formula to all cells in the column.
Step 4: Check Your Results
Now, you should see the first names in column B and the last names in column C. If you see any errors, make sure your names are formatted correctly and that you’re using the correct delimiter.
Wrapping Up
And there you have it! You’ve successfully separated full names into first names and last names in Google Sheets. This same technique can also be used to split any other type of data in Google Sheets. Happy splitting!