How to Split Cells and Extract Text Substrings in Google Sheets

Published on | ProSheetHub
Split Cells and Extract Text Substrings in Google Sheets thumbnail(prosheethub)

Raw datasets imported from web forms, payment gateways, or external CSV exports rarely arrive cleanly formatted. Full names are often lumped into a single cell, SKU numbers contain embedded category codes, and full addresses are squished together with commas.

To clean and prepare data for pivot tables or financial reporting, you need efficient methods to parse text strings. This guide covers how to split cells across columns using Google Sheets’ built-in features and dynamic formulas like SPLIT, LEFT, RIGHT, MID, and REGEXEXTRACT.

Method 1: The Quick Built-in Tool (Split Text to Columns)

If you need a quick, one-time fix without using formulas, Google Sheets features a built-in menu tool that splits delimited text instantly.

  1. Highlight the column containing the raw text you want to separate (e.g., A2:A100).
  2. Navigate to the top menu and select Data > Split text to columns.
  3. A small separator pop-up will appear near your highlighted range.
  4. Choose your delimiter (e.g., Comma, Space, Semicolon, or select Custom to enter special characters like hyphens - or pipes |).

Google Sheets will immediately divide the values across adjacent columns to the right.

Warning: The built-in "Split text to columns" tool overwrites data in adjacent cells to the right. Always ensure you insert empty columns next to your source data before running this tool.

Method 2: Dynamic Splitting with the SPLIT Formula

Unlike the menu tool, using the SPLIT function keeps your original source data intact and updates automatically whenever source text changes.

Formula Syntax

=SPLIT(text, delimiter, [split_by_each], [remove_empty_text])

Example 1: Splitting Email Addresses into Username and Domain

To separate john.smith@company.com into two columns in cell B2:

=SPLIT(A2, "@")

Example 2: Combining SPLIT with INDEX to Extract a Specific Segment

If you only want to retrieve the second item from a split string (e.g., extracting just the domain name from an email address) without spilling across multiple cells, wrap SPLIT inside INDEX:

=INDEX(SPLIT(A2, "@"), 2)

Method 3: Extract Substrings by Fixed Position (LEFT, RIGHT, MID)

When working with fixed-length codes, product SKUs, or structured IDs (e.g., ORD-2026-9842), extract specific characters based on their numerical position.

Function Formula Example Description Sample Output (from "ORD-2026-9842")
LEFT =LEFT(A2, 3) Extracts a specific number of characters starting from the left. "ORD"
RIGHT =RIGHT(A2, 4) Extracts a specific number of characters starting from the right. "9842"
MID =MID(A2, 5, 4) Extracts characters starting at position 5 for a length of 4. "2026"

Method 4: Advanced Substring Extraction with REGEXEXTRACT

When text structures are unpredictable or variable in length (e.g., pulling a order number out of a long notes field), regular expressions offer unmatched flexibility.

1. Extract Numbers Only from Mixed Text

To extract numeric digits from a string like "Invoice #84920 issued":

=REGEXEXTRACT(A2, "\d+")

2. Extract Text Inside Parentheses

To pull text enclosed in brackets like "United States (US)":

=REGEXEXTRACT(A2, "\((.*?)\)")

3. Extract Email Addresses from Unstructured Text

To find and pull an email address embedded inside a customer feedback comment:

=REGEXEXTRACT(A2, "[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}")

Master Text Extraction Method Comparison

Method Best Used For Dynamic?
Data > Split Text to Columns Quick, one-time dataset cleanup without formulas. No (Static)
SPLIT() Formula Separating delimited rows (commas, spaces, hyphens) across multiple columns. Yes
LEFT / MID / RIGHT Extracting fixed-length character codes from standardized SKUs. Yes
REGEXEXTRACT() Parsing complex pattern matches (emails, prices, numbers) from raw text. Yes

Troubleshooting Common Text Splitting Errors

  1. #REF! Error (Array result was not expanded):
    The SPLIT formula needs empty cells to the right to spill its output. Clear any existing data or formulas in adjacent columns.
  2. Numbers Format as Text:
    Values extracted using MID, LEFT, or SPLIT are returned as text strings. Wrap your formula inside VALUE() (e.g., =VALUE(RIGHT(A2, 4))) to convert extracted strings back into true numbers for calculations.
  3. #N/A Error in REGEXEXTRACT:
    This error occurs if the specified Regex pattern finds no match in the target cell. Wrap your formula in IFERROR to handle missing matches gracefully: 
    =IFERROR(REGEXEXTRACT(A2, "\d+"), "No Number Found")

Comments

Post a Comment