Graphed LogoGraphed
Login

How to Separate Data in Google Sheets

Cody Schneider•

Splitting text from a single cell into multiple columns is a requirement for nearly anyone working with data in Google Sheets. Whether you're cleaning up a messy data export or trying to organize contact information, a single column containing full names or addresses is not very useful for analysis. This guide will walk you through several effective methods to separate your data, from a simple built-in tool to more powerful and flexible formulas.

The Easiest Method: Using "Split text to columns"

Google Sheets has a fantastic built-in feature designed specifically for this task. It’s the fastest and most straightforward way to separate data when you have a consistent character, like a comma or space, dividing your information. This is the perfect tool for quickly breaking up simple structured data without writing a single formula.

When to use this method:

  • When you have a consistent delimiter (e.g., every full name is separated by a space, or every part of an address is separated by a comma).

  • When you need a quick, one-time split and don't require the data to update automatically if the source cell changes.

  • When you're not a fan of writing formulas and want a simple, menu-driven solution.

Step-by-Step Guide:

Let’s say you have a list of full names in Column A, and you want to separate them into a "First Name" column and a "Last Name" column.

Your data looks like this:

1 Full Name 2 Maria Hernandez 3 Kenji Tanaka 4 Priya Patel

Step 1: Select Your Data

First, click and drag to highlight the entire range of cells containing the data you want to split. In this case, you would select cells A2 through A4. It's important not to select the header row if you don't want it to be split as well.

Step 2: Access the Tool

With your data selected, navigate to the Google Sheets menu and click on Data > Split text to columns.

Step 3: Choose Your Separator (Delimiter)

As soon as you click, a small separator detection box will appear near your selected data. Google Sheets is pretty smart and will often correctly guess the delimiter. In our example, it will likely select "Space."

If its guess is wrong, you can click the dropdown menu to manually select from options like:

  • Comma

  • Semicolon

  • Period

  • Space

  • Custom

For something like an email address (e.g., jane.doe@example.com), you could select "Custom" and enter the "@" symbol to separate the username from the domain.

Once you select the correct separator, the data instantly splits into the adjacent columns.

Your sheet will now look like this:

1 Full Name 2 Maria Hernandez 3 Kenji Tanaka 4 Priya Patel

Pro Tip: Make sure you have enough empty columns to the right of your data. If you try to split data into columns that already contain information, Google Sheets will overwrite it. It’s always a good idea to insert a few blank columns beforehand just to be safe.

Going Further with Formulas for More Control

While "Split text to columns" is great for quick tasks, it has one major limitation: it’s static. If the original data changes, you have to repeat the process. Formulas, on the other hand, are dynamic. Once you set them up, they automatically update whenever the source data is modified. This is ideal for dashboards and ongoing reports.

Using the SPLIT Function

The SPLIT function is the formula-based equivalent of the "Split text to columns" tool. It allows you to break up text around a specified delimiter.

Syntax: =SPLIT(text, delimiter)

  • text: The cell containing the text you want to split.

  • delimiter: The character or characters you want to split by, written inside quotation marks.

Example: Splitting by a Hyphen

Imagine you have a list of product IDs where the product type, color, and size are joined by hyphens, like SWEATER-BLUE-XL.

Your data:

1 Product ID 2 SWEATER-BLUE-XL 3 PANTS-BLACK-MD 4 HAT-GREEN-OS

In cell B2, you would type the following formula:

When you press Enter, Google Sheets will automatically populate the data across the next three columns:

You can then drag the fill handle (the small blue square in the corner of cell B2) down to apply the formula to the rest of your product IDs.

Extracting Data with LEFT, RIGHT, and FIND

Sometimes you don’t need to split a whole cell, but rather just extract a specific piece of it. This is where text functions like LEFT, RIGHT, MID, and FIND come in handy. They give you surgical precision over your data.

How to Extract the First Word (e.g., First Name)

To get just the first name from a full name like "Maria Hernandez" in cell A2, you can't just tell the LEFT function to grab a set number of characters since every name has a different length. We need to tell it to grab everything before the first space.

The FIND function helps us locate that space. FIND(" ", A2) will return the position of the space character. For "Maria Hernandez", it returns 6 (M-a-r-i-a-space).

Now, we can combine this with the LEFT function.

Formula:

Let's break it down:

  • FIND(" ", A2) finds the position of the space in cell A2, which is 6.

  • We subtract 1 because we don't want to include the space itself, so we get 5.

  • LEFT(A2, 5) then extracts the first 5 characters from cell A2, which is "Maria."

How to Extract the Last Word (e.g., Last Name)

Grabbing the last name is a bit more complex. Here, we'll combine the RIGHT, FIND, and LEN functions. LEN simply calculates the total length of the text in a cell.

Formula:

Let's break this one down for "Maria Hernandez":

  • LEN(A2) calculates the total length of "Maria Hernandez," which is 15.

  • FIND(" ", A2) finds the position of the space, which is 6.

  • 15 - 6 equals 9. This gives us the length of the last name ("Hernandez").

  • RIGHT(A2, 9) then extracts the last 9 characters from cell A2, which results in "Hernandez."

These formulas might seem intimidating at first, but once you understand how they nest together, you unlock a powerful way to automate your data cleaning processes.

Practical Scenarios for Separating Data

Knowing the "how" is great, but let's look at some real-world examples where these techniques are incredibly useful.

1. Cleaning Up Marketing Campaign Data

Ever export data from your ad platform and get a single "Campaign Name" field like SummerSale_Email_USA? It’s hard to analyze. Using the SPLIT function or "Split text to columns" with a custom delimiter of _ separates it into three clean columns: Campaign, Channel, and Region.

In a new cell, type: =SPLIT(A2, "_")

2. Separating Components of an Address

Suppose you have a contact list with full addresses in one column: "1600 Amphitheatre Parkway, Mountain View, CA 94043". This is impossible to sort by city or state.

Simply highlight the column, go to Data > Split text to columns, and use the Comma separator. Instantly, you'll have columns for the street address, city, and State/Zip code (which you can split again with a space). This prepares your data for filtering, sorting, or mapping.

3. Decoding Product Variants from a SKU

E-commerce managers often deal with SKUs that contain bundled information, like VNK-TEE-MEN-RED-L. Sorting by size or color requires separating that data first. This is a perfect job for the SPLIT function with a hyphen delimiter.

This allows you to quickly build reports answering questions like, "How many Men's large t-shirts did we sell?" - a task that would be next to impossible with the original SKU format.

Final Thoughts

Mastering data separation in Google Sheets is a fundamental skill for anyone working with spreadsheets. From the easy one-click "Split text to columns" feature for simple tasks to the dynamic power of SPLIT, LEFT, RIGHT, and FIND formulas for more complex automation, you now have the tools you need to clean, organize, and prepare your data for meaningful analysis.

While mastering formulas for these tasks is a valuable skill, we also know that manual data prep is a major time sink that pulls you away from actual analysis. We built Graphed to connect directly to your data sources — like Google Analytics, Shopify, and your ad platforms — and handle all this structural work automatically. Instead of cleaning CSVs, you just use plain English to ask what you want to see, and Graphed creates the reports and dashboards for you without the formula headaches.