Can you combine first and last name in Google Sheets?

Watch Video – How to Separate First and Last Name using Split Text to Columns

Google Sheets makes life easy when it comes to doing some data slice and dice. It has tons of formulas and functionalities (some even better than the ones in Microsoft Excel spreadsheets).

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 three simple and easy ways to separate a name into First and Last name in Google Sheets.

• Using the SPLIT formula
• Using Split Text into Columns Feature
• Using Text Function

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 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 into first and last name, 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, and it would automatically fill the column B and C with first and last name.

Instead of copying the formula manually, you can also use the fill handle and drag it for all the cells in which you want the formula.

Note that when you use this formula, Google Sheets will not allow you to delete only the last name for any name. You can choose to 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 (based on the formula). So the last name is a part of the formula, and since the formula 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 (or else use this formula, then convert formula to values and then delete the ones you don’t want).

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.

Using Split Text into Columns Feature

Another powerful feature in Google Sheets is the ‘Split Text into Columns’ feature.

Split Text into Columns allows you to specify the delimiter that you want to use to split the text (names in our example).

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 ‘Split Text into Columns’ to separate first and last name:

1. Select the cells that contain the name that you want to split
2. Click the Data tab
3. Click on ‘Split Text into 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 SPLIT formula result, the result you get after using the ‘Split Text into Columns’ is static. This is a preferred method when you have a list and you only want to split the text once or twice. In case you want to split the text multiple times or on a recurring basis, it’s best to use a formula.

Below is a video where I cover how to use Text to Column to split the first and last names in Google Sheets.

Using Text Function

While SPLIT function is great, it gives you the entire split of the text.

For example, if 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 Sheet has.

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

Let me show you how Text function works 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 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 type for formulas. For example, if you have a mix of names with first/last name 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.

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