How to Use the TEXTSPLIT Function in Google Sheets to Split Strings Across Rows and Columns

Parsing delimited text strings—such as comma-separated tags, semicolon-delimited lists, or multi-line address blocks—is a standard task in data cleansing. While Google Sheets users historically relied on the classic SPLIT function, SPLIT is limited to spilling values horizontally across columns and cannot split across rows or handle distinct row-and-column delimiters concurrently. With the native TEXTSPLIT function, Google Sheets allows you to split text strings into two-dimensional arrays across both rows and columns in a single formula.

Understanding the Syntax of TEXTSPLIT

The TEXTSPLIT function accepts flexible delimiter parameters and optional error-handling controls:

• =TEXTSPLIT(text, [col_delimiter], [row_delimiter], [ignore_empty], [match_mode], [pad_with])
• text: The source text string you want to divide.
• col_delimiter: The character or substring used to split text horizontally across columns.
• row_delimiter: The character or substring used to split text vertically down into new rows.
• ignore_empty: Set to TRUE to ignore consecutive delimiters and prevent blank cells.

Splitting Values Across Columns and Rows Concurrently

Consider a structured string exported from a web database: "Apples,50;Oranges,30;Bananas,80" where semicolons separate records and commas separate item attributes:

1. Click in cell B2 where you want the resulting table to spill.
2. Enter the formula:
   =TEXTSPLIT(A2, ",", ";")
3. Press Enter. Google Sheets instantly parses the string into a clean 2-column by 3-row matrix: column 1 contains item names, while column 2 contains quantities.

Splitting by Multiple Alternate Delimiters

Real-world datasets often feature inconsistent formatting where entries are separated by different symbols (such as commas, slashes, or hyphens):

• Passing an Array of Delimiters: Supply multiple delimiters within curly braces to handle mixed formats:
  =TEXTSPLIT(A2, {",", "/", "-"})
• Splitting Multi-Line Text into Rows: Convert line-break delimited text into a vertical list using CHAR(10):
  =TEXTSPLIT(A2, , CHAR(10))
  Leaving the column delimiter blank instructs the formula to spill exclusively downward across rows.

Adopting TEXTSPLIT in Google Sheets replaces rigid text-to-columns wizards with responsive, dynamic formulas that update automatically whenever raw source data changes.