Let’s say you are organizing a big company event and have a list of attendees where each person’s full name is written in one cell (like “Puneet Gogia”). To make organizing easier, you need to personalize emails or create name tags, but you need the first and last names in separate cells.
Even though, while working with data where I have names, it happens a lot to me that I need to separate names into first and last names. I have realized we can use different methods in Google Sheets to solve this.
So, in this tutorial, we will learn all the methods you can use to split the full names into first and last names.
Using SPLIT Function to Separate Names in Google Sheets
The SPLIT function helps you to split text from one cell into multiple cells based on a specific character, known as a delimiter. In the example below, you can see that we have a full name in cell A1; when I used the SPLIT in cell B1, the first and last names in cell B1 and C1 were returned, respectively.
=SPLIT(A1," ")
In cell A1, we have a space between that name, and in our formula, we have used a pace as a delimiter. This tells the formula to use a space to split values into parts.
SPLIT is a dynamic function; when you enter it in cell B1, it automatically enters the second value into cell C1. This is not limited only to first and last names; if you have a name with a first, middle, and last name, you can still separate it into parts with this function.
In the above example, the formula is the same, but in cell A1, we have a name with three names in it, and when you enter formula, each name is separated in
If you want to split names from an entire column or a range of cells, you can use a combination of ARRAYFORMULA + SPLIT, as I have used in the following formula:
=ARRAYFORMULA(SPLIT(A1:A14," "))
It splits the full names from the range A1:A14 into first and last names using a space as the separator. The ARRAYFORMULA allows the SPLIT function to work across multiple rows simultaneously, dividing each name into first and last names.
Dealing with Titles and Suffixes
If titles and suffixes are consistent (e.g., always “Dr.” at the start and “Jr.” at the end), you can use the SPLIT function, but you will get multiple values, like title, first name, and last name, three different cells.
Separate First and Last Name with LEFT, RIGHT, and SEARCH
You can also use a formula combining LEFT, RIGHT, and SEARCH functions. To understand this, let’s use a formula to get the first name from the cell A1.
=LEFT(A1, SEARCH(" ", A1) - 1) This formula extracts the first name from a full name in cell A1 by finding the position of the first space and then extracting all characters before the space (subtracting 1 to exclude the space itself).
Now, let’s extract the last name from the full name. You can use the formula below, which combines RIGHT, LEN, and SEARCH.
This formula helps you get the last name from cell A1.
First, SEARCH(” “, A1) finds where the space is in the text (between the first and last name). Then, LEN(A1) counts the total number of characters in the cell.
By subtracting the position of the space from the total number of characters (LEN(A1) – SEARCH(” “, A1)), it calculates how many characters the last name has.
In the end, RIGHT takes count of these characters from the end of the text in A1, giving you the last name.
Using REGEXEXTRACT Function to Split Names into First and Last
In Google Sheets, REGEXEXTRACT is a powerful function that uses regular expressions to pull specific parts of text from a cell based on patterns you define.
=REGEXEXTRACT(A1, "^(\w+)")
Here, we have used ^(\w+), which starts matching from the beginning of the text (^) and continues until it encounters a space. Then, capture everything that gets below that space.
Then, we used the formula below to get the last name from cell A1.
=REGEXEXTRACT(A1, "\s(\w+)$")
Here, we have used \s(\w+)$, which makes the function look for a space (\s) followed by a sequence of letters or numbers (\w+) that appears at the end of the text ($). And then extract that in the result.
Use Split Text to Columns to Split Names
The “Split text to columns” splits the value of a cell into multiple cells based on a delimiter, such as a comma or space. And you can use it to split full names into first and last names.
To use this option, click on the column A header to select the entire column. Then, go to the top menu and click on Data. From there, select Split text to columns.
Then, you will get a small drop-down at the bottom of your screen asking you to choose a separator. Here, you need to select “Space,” as the name is separated by a space in our example.
You can see in the above example that when I selected the “Space” from the drop-down, it splits the name from cell A1 to the first and last names in cell A1 and B1, respectively.
Use a Google App Script to Split Names from a Column into First and Last
If you like to automate splitting names into first and last, you can use Google App Script.
function splitNames() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var range = sheet.getDataRange(); // Gets the range of cells that have data
var values = range.getValues(); // Gets all data in the range as a 2D array
var results = []; // Array to hold the results
// Loop through each row in the array
for (var i = 0; i < values.length; i++) {
var fullName = values[i][0]; // Assuming full names are in the first column
var parts = fullName.split(" "); // Split the full name by space
var lastName = parts.pop(); // Remove and store the last element as last name
var firstName = parts.join(" "); // Join the remaining elements as first name
results.push([firstName, lastName]); // Add the split names to the results array
}
// Optional: write the results to the sheet in columns B and C
var resultRange = sheet.getRange(1, 2, results.length, 2); // Define where to write results
resultRange.setValues(results); // Write the results to the sheet
} To enter this code, go to the Extension > Apps Script. There, paste this code into the code window. After that, File > Save to save the code and Click on the triangle button to run the code. Please refer to the below snap-shot to change it to fit your needs.
