How to Split First and Last Names in Google Sheets (3 Easy Ways)

Watch Video – How to Split First and Last Names in Google Sheets using Split Text to Columns

Google Sheets has tons of formulas and functionalities (some even better than the ones in Microsoft Excel spreadsheets) that can split text.

One such area where you may need some formula intervention could be when you need to separate names into first name and last name (or email address into the username and domain name).

In this tutorial, I will show you how to split first and last names in Google Sheets with four simple methods.

How to Split First and Last Names in Google Sheets Using the SPLIT formula

Google Sheets has an inbuilt function called SPLIT that allows you to instantly split the text based on a delimiter.

In the case of names, the delimiter is a space character. This means that the formula uses the space character to split the full name into first and last name (or however many parts are there to the name).

Let’s say you have a data set as shown below:

To split the full name and separate first and last names in Google Sheets, use the below formula in cell B2 in Google Sheets

=SPLIT(A2," ")

As soon as you enter this formula and hit enter, it will automatically split the name into first and last name. It will put the first name in cell B2 and the last name in cell C2.

You can now copy and paste this formula in all the cells in column B, or use the fill handle, and it will automatically fill columns B and C with first and last names.

Note that when you use this formula, Google Sheets will not allow you to delete only the last name of any name. You can delete the first name in Column B or both first and last name, but not just the last name.

This is because when you enter the formula in any cell in column B, it automatically adds the one in column C. So the last name is a part of the formula, and since the formula is in column B, you will have to delete it from there only.

One benefit of using the SPLIT function to split names (or any other text) is that it makes your results dynamic. This means that if you change any name, the results will automatically update to reflect the change.

Also, note that this formula will give you all the split values. For example, if you only want the first name or the last name, you can’t use the SPLIT function.

If you only want to get a part of the text (let’s say only the first name or only the last name), use the TEXT formulas method shown later in this tutorial.

How to Split First and Last Names Using Google Sheets Split Text to Columns

Another powerful feature in Google Sheets is the “Split Text to Columns” feature.

Google Sheets Split Text to Columns allows you to specify the delimiter that you want to use to separate columns or text in Google Sheets (names in our example).

You can separate columns using a comma, semicolon, period, space, or custom delimiter.

Google Sheets then automatically uses the specified delimiter and splits the text.

Suppose you have the names as shown below, and you want to separate the name into first name and last name.

Here are the steps to use the Google Sheets Split Text to Columns feature to separate first and last names:

1. Select the cell containing the name that you want to split
2. Click the Data tab
   

   

3. Click on ‘Split Text to Columns’ option

4. In the Separator box that appears, select Space as the delimiter
   

   

The above steps would instantly split the full name into first and last name (in different columns).

Unlike the SPLIT formula result, the result you get after using the ‘Split Text to Columns’ is static, as shown above. This is a preferred method when you have a list, and you only want to split the text once or twice. If you want to split the text multiple times or repeatedly, it’s best to use a formula.

How to Split First and Last Name in Google Sheets Using Text Function

While the SPLIT function is a great way to split text to columns in Google Sheets, it gives you the entire split of the text.

For example, say you only want the first name or only the last name, then you can’t do it directly with the SPLIT function. You can still use it by converting the formula to values and then deleting the cells you don’t want, but that just makes it less efficient.

In such cases, you can use the wonderful TEXT functions that Google Sheets has.

These Text functions allow you to extract only that part of the text that you want.

Let me show you how to separate text in Google Sheets using the Text function with examples.

Suppose you have the data set as shown below:

You can use the below formula to extract only the first name from the full name:

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

The above formula uses the FIND function to get the position of the space character in the name. For example, in the name ‘Jesusa Owenby’, the space character is in the seventh position.

Now we use this position number to extract all the characters which are to the left of it, using the LEFT function.

Similarly, if you only want the last name, you can use the following formula:

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

The above formula uses the same concept with a slight twist. Since we need to get the last name, we need to find the number of characters to get after the space character.

So, I have first used the FIND function to get the position of the space character and then used the LEN function to find how many characters are there in the name. A simple subtraction of the FIND formula result from the LEN formula result gives me the number of characters in the last name.

Then I simply use the RIGHT function to get all the characters from the right (after the space character).

The TEXT functions allow you a lot of flexibility and you can handle a lot of scenarios with these types for formulas. For example, if you have a mix of names with first/last names and ones with a middle name as well, you can still use a variation of these TEXT formulas to get the first and the last name.

How to Split the First and Last Name in Google Sheets Using Split Names Addon

Like with most functions in Google Sheets, you can also use an add-on to split names.

1. Go to Extensions > Add-on

2. Select Get add-ons
3. Search for split names in the Google Marketplace search bar

4. Select the Split Names add-on and click Install to download it

5. Google might ask you for permission to install the addon, in which case you can click continue and select your Google account.

6. Then click Allow

7. Once you have installed the add on, select the list of names that you want to split
8. Go to Extensions, and you should see Split Names in the menu. Click it, then click Start.

9. The split names window has several choices. For our example, we will select First name and Last name. If you have names as well you can also add a middle name column.

10. Once you’re done, click split. The add-on will take a few seconds to load before splitting your names.

The Split Names addon is useful because it can differentiate between first names. Last names and middle names. It also recognizes over 80 titles and 40 post-nominals. Unfortunately, it isn’t free, but it has a free 3-day trial. If you want Google Sheets split first and last name, you don’t really need to pay for this addon since you can use the first three options. However, if you want to split jumbled names into first and last names, then this addon is able to do so.

Conclusion

In this article, we have shown you how to split first and last names in Google Sheets: using the split formula, using the Google sheets split text to columns feature, using the TEXT function, and using the split names add-on. Now that you know how to split full names in Google Sheets, you might also be interested in our article that shows how to transpose in Google Sheets.

You may also like the following Google Sheets tutorials:

• • How to Delete Empty Rows in Google Sheets
  • How to Indent Text in Google Sheets
  • Convert Formulas to Values in Google Sheets
  • How to Transpose Data in Google Sheets
  • How to Get the Word Count in Google Sheets