TEXTSPLIT

Splitting text used to require either the Text to Columns wizard — a manual, one-time operation — or a complex combination of LEFT, MID, FIND, and LEN functions that quickly became hard to read and maintain. TEXTSPLIT changes this completely. It is a dynamic array function that splits a text string by any delimiter you choose, spilling the results into multiple cells automatically. It handles commas, spaces, pipes, line breaks, and even multiple delimiters at once.

TEXTSPLIT Syntax

TEXTSPLIT has two required arguments and four optional ones. The first two — the text to split and the column delimiter — are all you need for most everyday splitting tasks.

=TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty],
           [match_mode], [pad_with])

  text           →  The text string or cell reference to split
  col_delimiter  →  Split across columns (e.g. comma, space, pipe)
  row_delimiter  →  Split across rows (optional — creates a 2D result)
  ignore_empty   →  TRUE to skip blank tokens from double delimiters
  match_mode     →  0 = case-sensitive (default),  1 = case-insensitive
  pad_with       →  Value to fill missing cells in uneven splits

Splitting Across Columns

The most common use is splitting a delimited string across columns — CSV-style data, address components, full names, or tagged lists that arrive as a single cell of text.

A2 = "Sarah,Johnson,North,119000"

=TEXTSPLIT(A2, ",")
→ Spills across 4 columns: Sarah | Johnson | North | 119000

A2 = "Sarah Johnson"
=TEXTSPLIT(A2, " ")
→ Spills: Sarah | Johnson

A2 = "GB|NORTH|2024|LAMP"
=TEXTSPLIT(A2, "|")
→ Spills: GB | NORTH | 2024 | LAMP

Splitting Across Rows

The third argument — row_delimiter — splits text downward into rows instead of (or in addition to) across columns. This is useful when text uses one separator for rows and another for columns, like structured data pasted from another system.

A2 = "North:East:South:West"

=TEXTSPLIT(A2, , ":")      →  Splits into 4 rows (col_delimiter omitted)
North
East
South
West

A2 = "Priya,119000:James,117000:Sarah,93000"

=TEXTSPLIT(A2, ",", ":")
→  Splits into a 3-row × 2-column array:
Priya   | 119000
James   | 117000
Sarah   | 93000

Multiple Delimiters at Once

TEXTSPLIT accepts an array of delimiters — wrapping multiple separator characters in curly braces tells it to split on any of them. This handles messy real-world data where the same string might use commas in some places and semicolons or spaces in others.

A2 = "Sarah; Johnson, North / 2024"

=TEXTSPLIT(A2, {";", ",", "/"})
→  Sarah |  Johnson |  North |  2024
   (splits on semicolon OR comma OR slash)

A2 = "apple banana  cherry"   (double space between banana and cherry)

=TEXTSPLIT(A2, " ", , TRUE)
→  apple | banana | cherry
   (ignore_empty TRUE removes the blank token from the double space)

The ignore_empty argument (fourth, TRUE or FALSE) controls what happens when two delimiters appear next to each other — like a double space or a trailing comma. Setting it to TRUE silently drops the empty token so you do not get blank cells in the output. For most data cleaning work, TRUE is the safer default.

Splitting a Full Name — Practical Example

In Lesson 11 we split names using LEFT, MID, FIND, and LEN — a formula that works but takes several steps. TEXTSPLIT does the same job in one line and handles names regardless of length.

Splitting Tags and Lists From a Single Cell

A very common real-world scenario: a notes or tags field where multiple values are crammed into one cell, separated by commas or semicolons. TEXTSPLIT turns these into proper separate values that can then be analysed with FILTER, UNIQUE, or COUNTIF.

A2 = "Excel, Power Query, DAX, Power BI"

=TEXTSPLIT(A2, ", ")
→  Excel | Power Query | DAX | Power BI

Trimming spaces from each part using TRIM:
=TRIM(TEXTSPLIT(A2, ","))
→  Each result has leading/trailing spaces removed

Count how many tags are in the cell:
=COUNTA(TEXTSPLIT(A2, ","))   →  4

Wrapping TEXTSPLIT inside TRIM removes any spaces that might be left around each token — essential when splitting comma-space delimited text where the space is part of the separator pattern but might vary. You can nest TEXTSPLIT inside almost any function that accepts an array.

The pad_with Argument — Handling Uneven Splits

When TEXTSPLIT creates a 2D result and different rows produce different numbers of tokens, the shorter rows need padding to fill out the rectangle. The sixth argument — pad_with — controls what fills those empty spots. By default it shows #N/A, which is rarely what you want.

Uneven rows — some have 3 parts, some have 2:
A2 = "North,Priya,119000:East,James:South,Maria,82000"

=TEXTSPLIT(A2, ",", ":")
→  North | Priya | 119000
   East  | James | #N/A      ← missing third token, shows #N/A
   South | Maria | 82000

Fix with pad_with — use empty string instead of #N/A:
=TEXTSPLIT(A2, ",", ":", , , "")
→  North | Priya | 119000
   East  | James |           ← blank instead of #N/A
   South | Maria | 82000

TEXTSPLIT With Other Functions

Because TEXTSPLIT returns a dynamic array, its output can be passed directly into other functions — no intermediate columns needed. This makes it a building block in formula pipelines.

A2 = "North,East,South,North,East,North"

Unique regions from a comma-separated cell:
=UNIQUE(TEXTSPLIT(A2, ","))         →  North | East | South

Sorted unique regions:
=SORT(UNIQUE(TEXTSPLIT(A2, ",")))   →  East | North | South

Count occurrences of "North" in the cell:
=COUNTIF(TEXTSPLIT(A2, ","), "North")  →  3

Convert a comma-separated number list to a SUM:
A2 = "42000,38500,51000,47200"
=SUM(VALUE(TEXTSPLIT(A2, ",")))     →  178700

The last example — using VALUE to convert split text tokens into numbers and then SUM to total them — is a technique that appears constantly when processing data exported from web applications or databases where numbers arrive stored as text strings.

Next up — TEXTBEFORE and TEXTAFTER, the precise modern alternatives to LEFT/MID/RIGHT that extract text relative to a delimiter without needing to count characters.