How to Separate Names in Google Sheets: A Step-by-Step Guide

Published: January 13, 2024 - 5 min read

Julian Alvarado

Google Sheets is a powerful tool that allows users to perform various operations on data, including splitting full names into first and last names. This feature is particularly useful for organizing data and making it easier to sort and filter. If you are looking to separate first and last names in Google Sheets, there are several ways to do so.

Learn How To Separate First and Last Names in Google Sheets

There are several ways to achieve this, but two of the most popular methods are using the SPLIT function and Text to Columns.

Using SPLIT Function to Separate Names

The SPLIT function is a powerful tool for learning how to split full name in google sheets. It teaches you how to separate text into different columns. It works by using a delimiter to split the text into an array. In the case of separating first and last names, the delimiter is usually a space.

To use the SPLIT function, select the cell containing the full name and enter the following formula:

=SPLIT(A2,” “)

This will split the full name into two columns, with the first name in one column and the last name in another. The formula can be copied and pasted into other cells to quickly split multiple names.

Text to Columns for Name Separation

Another way to separate first and last names in Google Sheets is by using the Text to Columns feature. This feature allows you to split text into separate columns based on a delimiter.

To use Text to Columns, select the cell containing the full name and follow these steps:

  1. Click on the “Data” tab in the top menu.
  2. Select “Split text to columns.”
  3. In the “Separator” box, select “Space” as the delimiter.
  4. Click “Split” to separate the text into two columns.

Text to Columns is a quick and easy way to split names, but it may not be as versatile as using the SPLIT function. For example, if the names are separated by a different delimiter, such as a comma, Text to Columns may not work as well.

Overall, both the SPLIT function and Text to Columns are useful tools for separating first and last names in Google Sheets. By understanding how these functions work, users can efficiently organize and analyze their data.

It’s also worth noting that Google Sheets has a wide range of other functions that can be used to manipulate data, such as the SUM function for adding up the values of cells. By mastering these functions, users can unlock the full potential of Google Sheets and streamline their workflow.

Advanced Techniques for Splitting Names

If you need to separate first and last names in Google Sheets, there are a few advanced techniques you can use to streamline the process. In this section, we’ll explore two methods for extracting first and last names: using formulas and automating name separation with scripts.

Using Formulas to Extract First and Last Names

One way to split full names in Google Sheets is to use formulas. The LEFT, RIGHT, FIND, and LEN functions can be used to extract the desired components from a full name. Here’s how to do it:

  1. Insert a new column next to the column containing the full names.
  2. In the first cell of the first name column, use the formula =LEFT(A2,SEARCH(” “,A2)-1) to extract the first name. Adjust the cell references accordingly.
  3. In the first cell of the last name column, use the formula =RIGHT(A2,LEN(A2)-SEARCH(” “,A2)) to extract the last name. Again, adjust the cell references as needed.

By using formulas, you can quickly split full names into their individual components without having to manually enter the data.

Automating Name Separation with Scripts

If you frequently need to split full names in Google Sheets, you can automate the process using scripts. Here’s how to do it:

  1. Open your Google Sheet and go to Tools > Script editor.
  2. In the script editor, paste the following code:


function splitNames() {

  var sheet = SpreadsheetApp.getActiveSheet();

spreadsheet ai
Free AI-Powered Tools Right Within Your Spreadsheet

Supercharge your spreadsheets with GPT-powered AI tools for building formulas, charts, pivots, SQL and more. Simple prompts for automatic generation.

  var range = sheet.getDataRange();

  var values = range.getValues();

  for (var i = 1; i < values.length; i++) {

    var name = values[i][0];

    var splitName = name.split(” “);

    sheet.getRange(i+1, 2).setValue(splitName[0]);

    sheet.getRange(i+1, 3).setValue(splitName[1]);

  }

}

  1. Save the script and run it by going to Run > splitNames.
  2. The script will split the full names in column A into separate columns for first and last names.

By automating the name separation process, you can save time and reduce the risk of errors when working with large datasets.

In conclusion, using formulas and scripts are two advanced techniques that can help you split full names in Google Sheets quickly and accurately. Whether you’re working with a small or large dataset, these methods can help you streamline your workflow and improve your productivity.

Sync Live Data into Your Spreadsheet

Connect Google Sheets or Excel to your business systems, import your data, and set it on a refresh schedule.

Try the Spreadsheet Automation Tool Over 500,000 Professionals are Raving About

Tired of spending endless hours manually pushing and pulling data into Google Sheets? Say goodbye to repetitive tasks and hello to efficiency with Coefficient, the leading spreadsheet automation tool trusted by over 350,000 professionals worldwide.

Sync data from your CRM, database, ads platforms, and more into Google Sheets in just a few clicks. Set it on a refresh schedule. And, use AI to write formulas and SQL, or build charts and pivots.

Julian Alvarado Content Marketing
Julian is a dynamic B2B marketer with 8+ years of experience creating full-funnel marketing journeys, leveraging an analytical background in biological sciences to examine customer needs.
500,000+ happy users
Wait, there's more!
Connect any system to Google Sheets in just seconds.
Get Started Free

Trusted By Over 50,000 Companies