How to Split Data in Google Sheets

Got a messy spreadsheet where info like first and last names, addresses, or product SKUs are all lumped together in one cell? This is a common headache, especially when you’re working with data exported from a CRM, a web form, or another software platform. Before you can analyze or use that data, you need to clean it up. We'll show you four different ways to split data in Google Sheets, ranging from a quick two-click solution to powerful formulas that can handle any text pattern you throw at them.

Method 1: The Quickest Fix for One-Time Splits with 'Text to Columns'

If you have a simple, one-time task and don't need your data to update automatically if the original cell changes, the built-in 'Split text to columns' feature is your best friend. It’s perfect for quickly separating data based on a common character like a comma, space, or semicolon.

Let’s say you have a list of full names in column A that you want to split into a 'First Name' column and a 'Last Name' column.

How it Works:

1. Select your data: Highlight the entire column (or the specific range of cells) that you want to split. In our example, you would select cells A2 through A5.
2. Find the tool: In the Google Sheets menu, navigate to Data > Split text to columns.
3. Choose your separator: As soon as you click, a small Separator box will appear. Google Sheets will try to 'Detect automatically,' but you can manually select from a dropdown list (Comma, Semicolon, Period, Space).

For our example, Google Sheets will correctly guess that the separator is a Space. Your data will instantly be split across the adjacent columns.

When to use this method: Use ‘Text to Columns’ for quick clean-up jobs where the data is consistently formatted. It's the fastest way to get a simple split done without writing a single formula.

Keep in mind: This is a one-time, static action. If you change a name in the original column A (e.g., from "Maria Garcia" to "Maria Lopez"), the split columns B and C will not update. They are no longer connected to the source.

Method 2: The Go-To Formula for Dynamic Splitting with SPLIT

For a more dynamic solution that updates automatically, the SPLIT function is the way to go. This formula takes text from one cell and breaks it into multiple cells based on a delimiter you define. Unlike Text to Columns, if the source data changes, the SPLIT formula's output will update instantly.

How it Works:

The basic syntax for the function is:

=SPLIT(text, delimiter)

• text: The cell containing the data you want to split.
• delimiter: The character (in quotes) that you want to split by. This could be a space " ", a comma ",", a hyphen "-", or any other character.

Following our earlier example, click into cell B2 and type this formula:

=SPLIT(A2, " ")

Press Enter, and "Maria Garcia" from A2 will be split into "Maria" in B2 and "Garcia" in C2. You can then click the small blue square on the bottom-right corner of cell B2 and drag it down to apply the same formula to the rest of the names.

Practical Examples for the SPLIT Function:

• Splitting email addresses: To separate a username from its domain in the email hello@website.com, you'd use the "@" symbol as the delimiter.
• Splitting comma-separated values (CSVs): If a cell contains "Red,Blue,Green", you can create three separate cells with this formula:
• Splitting URL paths: To break down a URL endpoint like /blog/categories/data into its components, use the forward slash "/" as a delimiter:

When to use this method: The SPLIT function is the most common and versatile tool for splitting text in Google Sheets. Use it whenever you need your split data to stay linked and update automatically with the source cell.

Method 3: The Surgical Approach for Precise Extraction with LEFT, RIGHT, & FIND

Sometimes you don't need to split a cell into all its parts - you just need to extract the part to the left or right of a specific character. This is where combining the LEFT, RIGHT, and FIND functions gives you surgical precision.

How it Works:

These formulas work together. First, FIND locates the position of your delimiter. Then, LEFT or RIGHT uses that position to grab the exact number of characters you need.

Extracting the Text to the Left

Let’s say you have an email address in cell A2 like sandra.cho@example.com and you only want to extract the username, sandra.cho.

1. First, use FIND to locate the "@" symbol: =FIND("@", A2). This will return 12, because "@" is the 12th character.
2. Next, you tell the LEFT function to grab everything from the left of the cell, up to that position. But since you don't want the "@" itself, you subtract 1 from the FIND result.

The final formula looks like this:

=LEFT(A2, FIND("@", A2) - 1)

This tells Google Sheets: "Start from the left of cell A2 and give me the first 11 characters." The result is sandra.cho.

Extracting the Text to the Right

Now, let's grab the domain, example.com. This is a little trickier. We need to tell the RIGHT function how many characters to grab, which varies for each domain. To figure this out, we calculate the total length of the text (LEN) and subtract the position of the "@" symbol (FIND).

The formula becomes:

=RIGHT(A2, LEN(A2) - FIND("@", A2))

This tells Google Sheets: "Take the total length of the text in A2 (23), subtract the position number of the '@' symbol (12), and then grab that many characters (11) from the right." The result is example.com.

When to use this method: Use this combination of formulas when you only need one piece of the data from a cell, especially if the text lengths are inconsistent. It gives you more control than the SPLIT function.

Method 4: The Most Powerful Way for Complex Patterns with REGEXEXTRACT

What if your data isn't separated by a simple delimiter? What if you need to pull a product ID from the middle of a messy text string? For these complex situations, Regular Expressions (RegEx) offer almost limitless power. The REGEXEXTRACT function lets you use these patterns to pull out precisely the information you need.

While RegEx can seem intimidating, you only need to learn a few basics to unlock its power.

How it Works:

The syntax is an extension of the other formulas:

=REGEXEXTRACT(text, regular_expression)

Example: Extracting a Product SKU

Imagine your data in column A looks like this: "Order confirmed for Product (SKU-2024-BETA-7) - tracking #98221." The SKU format is consistent, but its position in the sentence might change. You need to extract just "SKU-2024-BETA-7".

A simple regular expression for this would be: "(SKU-.*?-.*?-.*?)"

Here’s what that pattern means:

• ( ): This creates a "capture group," telling the function what part of the matched pattern you actually want to extract.
• SKU-: This looks for the literal characters "SKU-".
• .*?: This is the magic part. . matches any character, * means zero or more times, and ? makes it "non-greedy" so it stops at the next hyphen. Effectively, it means "match anything until you hit the next hyphen".

Your formula would be:

=REGEXEXTRACT(A2, "(SKU-.*?-.*?-.*?)")

This will scan the text in cell A2, find the pattern starting with "SKU-" followed by segments separated by hyphens and extract it flawlessly, no matter what other text surrounds it.

When to use this method: REGEXEXTRACT is your tool for messy, semi-structured, or inconsistent data. It’s perfect for pulling out phone numbers, zip codes, specific codes, or any information that follows a known pattern within a larger block of text.

Final Thoughts

Mastering these data splitting techniques in Google Sheets opens up a huge range of possibilities for cleaning and preparing your data for analysis. Moving from basic tools like Text to Columns to powerful and dynamic formulas like SPLIT and REGEXEXTRACT gives you the flexibility to organize nearly any dataset you might encounter.

While cleaning messy data is a vital skill for any marketer or analyst, the real goal is to find insights, not to spend hours wrestling with spreadsheet formulas. That's why our approach with Graphed is different. We automate the parts that slow you down by connecting directly to your tools like Shopify, Google Analytics, and Hubspot, so your data is already clean and ready for analysis. Instead of exporting CSVs and manually splitting columns, you just ask Graphed a question in plain English, and it builds the real-time report for you. It's built to get you from raw data to a clear answer instantly.