4 methods to separate first and last names in Google Sheets
- Using the SPLIT formula
- Using Split text to columns
- Using the Split Names add-on
- Using Text functions
Method 1: Using the SPLIT formula
This is a straightforward and easy method to separate the first and last names.
Step 1: Identify the data you want to split.
For this example, the names list is in column A. We will split the name and display the First name is Column B and Last name in Column C.
Step 2: Use the SPLIT the formula.
We will use the SPLIT formula, which is =SPLIT(A1, “ ”).
- A1 represents the cell to be separated.
- The apostrophes specify where it should split. It is called the delimiter.
Here, there is a space between the apostrophes because the names in column A are separated by a space.
If you have a comma separating the two names, you can specify a comma instead of a space. The formula would become =SPLIT(A1, “,”).
Step 3: Press Enter.
After typing in the formula, press Enter. You can now see the formula in action.
Step 4: Apply the formula to the column.
You can apply the formula to the entire column by dragging the fill handle. You can find the fill handle at the bottom right of a highlighted cell.
Drag it down till the end of your data.
You have successfully applied the SPLIT formula to the entire column.
Method 2: Using Split text to columns
Google Sheets also provides a built-in tool to separate names. IN this method, the last name moves to the next column and the first name remains in the original column.
Step 1: Highlight the cell with data to be split.
Here, A1 – A7 are highlighted. You can see the highlighted cells’ names in the formula bar at the top left.
Step 2: Select Split text to columns.
From the main menu, click Data. This will open a dropdown. Select Split text to columns.
Step 3: Select Separator.
You can see a small box asking you to choose the Separator. Click the two arrows at the corner to open the list of separators in a dropdown.
Choose the separator according to your data. Since a blank space is used here, select Space.
You can now see the names split and in separate columns.
Method 3: Using the Split Names add-on
You can also split names by installing the required add-on.
Step 1: Select Get add-ons.
From the main menu, click Add-ons. Now, select Get add-ons.
Step 2: Install the Split Names add-on.
Selecting Get add-ons will open a dialog box. Search for Split Names in the search box. Find the Split Names add-on by Ablebits and click on it.
Click the blue Install button to install the Split Names add-on.
It might prompt you for access or permission. Allow access to use the add-on. After successful installation, you can see a message.
Step 3: Select the range of cells to be split.
Identify and select the range of cells with the names that you want to separate.
Step 4: Start the Split Names add-on.
From the main menu, click Add-ons. Select Split Names, followed by Start.
Step 5: Customize and click Split.
Clicking Start will open a dialog box with multiple options. Select the ones that suit your needs. Check the first name and last name checkboxes.
If your data has a header, check that box as well. Finally, click the blue Split button.
Step 6: Check if the changes have been made successfully.
The add-on will show the number of changes made.
You can check your data to see the changes.
Method 4: Using Text functions.
You can use this method if you want just the first name or the last name instead of both.
Step 1: Identify the cell to display the first name or last name.
Here, cell B1 is chosen to display the name. You’ll be entering the formula in this chosen cell.
Step 2: Enter the formula.
The formula to extract the first name is =LEFT(A1,FIND(” “,A1)-1).
The formula to extract the last name is =RIGHT(A1,LEN(A1)-FIND(” “,A1)).
Choose and use the formula accordingly. Using the last name’s formula here.
Step 3: Press Enter.
Press Enter to see the formula in action. You might get an auto-fill prompt. Press Ctrl + Enter on your keyboard or click the tick. This will apply the formula to all the data present.
You have now successfully extracted the last name from your data.
Wrapping up
No more manually separating the first and last name from your data. You now have four methods at hand to automatically split all the names easily.