// Sheets · Apps Script

Split a full name into first and last columns in Google Sheets.

Split full names into first and last name columns in Google Sheets by splitting on the last space, so middle names and two-word first names land in the right column every time.

“I have a column of full names and need to split them into separate first and last name columns without mangling names like "Mary Jo Smith" or "Jean-Pierre Dupont."”

The script

copy · paste · trigger
splitNames.gs
Apps Script
// splitNames.gs
// Reads column A (full names), writes first name to B, last name to C.
// Splits on the LAST space so middle names stay with the first name.
function splitNames() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const lastRow = sheet.getLastRow();
  const names = sheet.getRange(2, 1, lastRow - 1, 1).getValues();

  const results = names.map(function(row) {
    const full = row[0].toString().trim();
    const lastSpace = full.lastIndexOf(' ');
    if (lastSpace === -1) {
      return [full, ''];
    }
    const first = full.substring(0, lastSpace);
    const last = full.substring(lastSpace + 1);
    return [first, last];
  });

  sheet.getRange(2, 2, results.length, 2).setValues(results);
}
Generate yours

Need a variant? Gnaw writes a custom version from one sentence — fields, triggers, edge cases handled.

Walkthrough

Why splitting on the first space fails

The built-in SPLIT formula and most tutorials do the same thing: find the first space, treat everything before it as the first name, everything after as the last name. That works fine for "John Smith." It breaks immediately for "Mary Jo Smith" (last name becomes "Jo Smith") or "Jean Pierre Moreau" (last name becomes "Pierre Moreau"). The first space is the wrong anchor point when your data comes from a form or a CRM that accepted whatever people typed.

The correct anchor is the last space. Everything to its left is the first name (including any middle names or compound given names). Everything to its right is the last name. This is not a perfect heuristic for every culture, but it is the right default for Western name data where the surname is always the final token.

Paste and run the script

Open your spreadsheet and go to Extensions > Apps Script. Delete the placeholder code and paste the full script above. Save it (Ctrl+S or Cmd+S), then click Run. The first time you run it, Google will ask you to authorize the script to read and modify your sheet — that is expected, click through.

The script reads from row 2 downward on column A (assuming row 1 is a header), then writes first names to column B and last names to column C. If those columns already have data, the script overwrites them, so make sure B and C are free before running. Column A is untouched.

One call to getRange plus setValues handles the entire column in a single batch operation. I have watched people write row-by-row loops that trigger a quota error on sheets with a few thousand rows — getValues/setValues on a block range avoids that entirely.

What happens with edge cases in your data

Single-word names (no space at all): lastIndexOf returns -1, and the script puts the whole value into the first-name column with an empty string for last name. That is a reasonable default for a name like "Cher" or "Zendaya" and avoids a crash.

Extra leading or trailing whitespace: the trim() call removes it before the split, so a name entered as " Smith, John " (with a leading or trailing space) won't produce a phantom empty token. It will, however, put "Smith," in the first-name column if someone entered last-name-first with a comma — that format needs a separate normalization step before running this script.

Hyphenated last names like "O'Brien-Walsh": no problem, because the split is purely on spaces. The hyphen is invisible to lastIndexOf. Hyphenated first names work the same way.

Want a custom version?

Describe your sheet and the rule you want. Gnaw writes the Apps Script — fields, triggers, edge cases — in one shot.

Generate yours Browse BullPack

FAQ

4 questions
Can I do this with a formula instead of a script?
Yes, but it is awkward. In a locale that supports TEXTBEFORE/TEXTAFTER (Excel and newer Google Sheets): =TEXTBEFORE(A2," ",-1) gives the first name and =TEXTAFTER(A2," ",-1) gives the last name. The -1 counts from the right. If those functions are not available in your Sheets version, you need a nested MID/FIND/LEN formula that is painful to read and maintain. The script is cleaner for anything over a few rows.
My names are in a different column, not A. How do I change that?
In the getRange call, the second argument is the column number (1 = A, 2 = B, 3 = C, and so on). Change the 1 to whatever column your names are in. For the output, change the 2 in the setValues getRange to the first output column number.
Will this run automatically when new rows are added?
Not as written. To make it automatic, go to the Apps Script editor, click the clock icon (Triggers), and add a time-driven trigger or an onEdit trigger. An onEdit trigger fires every time a cell changes, which lets you split names as they are entered, but you would need to add logic to check that the edit is in column A before running the split.
What about names where the last name comes first, separated by a comma, like "Smith, John"?
This script does not handle that format. You would need to detect the comma, split on it, and then swap the parts. A simple pre-processing step is to run a SUBSTITUTE(A2,", "," ") substitution to remove the comma and flip-back the order, or handle it in the script with an if (full.indexOf(',') !== -1) branch before the lastIndexOf call.

Related scripts