Text

TEXTSPLIT Function in Excel

Splits text into rows or columns using delimiters.

Syntax

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

Arguments

  • text (required): Text to split
  • col_delimiter (required): Delimiter for columns
  • row_delimiter (optional): Delimiter for rows

Examples

  • =TEXTSPLIT("a,b,c", ",") - Split by comma - Result: a | b | c
  • =TEXTSPLIT("a-b;c-d", "-", ";") - Split rows and cols - Result: 2x2 array

TEXTSPLIT best practices

  • Use TEXTSPLIT to split delimited text into rows, columns, or both.
  • Use the if_empty argument when repeated delimiters may create blank output cells.

Excel TEXTSPLIT function documentation

  • TEXTSPLIT breaks text into cells by column and row delimiters — dynamic array spill in Microsoft 365.
  • Syntax: =TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty], ...).
  • Replaces nested LEFT/MID/FIND chains for CSV-like strings, paths, and tags.
  • Related: [TEXTBEFORE](/functions/textbefore/), [TEXTAFTER](/functions/textafter/), [FILTER](/functions/filter/).

TEXTSPLIT syntax and spill behavior

  • text: string or cell to split.
  • col_delimiter: character or array of delimiters for columns — comma, space, custom.
  • row_delimiter optional: split into rows instead of wide spill.
  • Requires empty destination — #SPILL! if blocked; see [filter spill error](/problems/filter-spill-error/).

Step-by-step: split comma-separated tags

  • Step 1 — Tags in A2 like "Red,Blue,Green".
  • Step 2 — =TEXTSPLIT(A2, ",") spills across columns.
  • Step 3 — Trim spaces: wrap with TRIM if imports include spaces after commas.
  • Step 4 — Multiple delimiters: =TEXTSPLIT(A2, {",",";"}).
  • Step 5 — Combine with UNIQUE for distinct tag list.

TEXTSPLIT vs Text to Columns vs Power Query

  • TEXTSPLIT — live formula; updates when source changes.
  • Text to Columns — one-time static split.
  • Power Query — best for repeated large file imports.
  • Legacy: LEFT + FIND + MID for pre-365 workbooks.

Worked examples to copy

  • CSV cell: =TEXTSPLIT(A2, ",").
  • Email domain: =TEXTSPLIT(A2, "@") returns local and domain parts.
  • Path parts: =TEXTSPLIT(A2, "/").
  • Rows: =TEXTSPLIT(A2, ",", , , , , , TRUE) per row delimiter syntax in your build.

People also ask

  • TEXTSPLIT in Excel 2019? — No; Microsoft 365 / Excel 2021+.
  • TEXTSPLIT #SPILL!? — Clear cells below/right of formula.
  • TEXTSPLIT vs TEXTTOCOLS? — TEXTTOCOLS is newer UI-oriented split in some builds.
  • Empty segments? — ignore_empty parameter controls consecutive delimiters.

Common errors

  • Returns array - needs spill space

Use cases

  • Parse CSV data
  • Split addresses
  • Data transformation

Frequently asked questions

  • How do I split a comma-separated list into separate cells? Use =TEXTSPLIT(A1, ",") to split horizontally into columns. Add TRUE for ignore_empty to skip blank entries: =TEXTSPLIT(A1, ",",,TRUE). The result spills across multiple cells automatically.
  • How do I split text into rows instead of columns? Use the row_delimiter parameter: =TEXTSPLIT(A1, , CHAR(10)) splits by line breaks into rows. Or =TRANSPOSE(TEXTSPLIT(A1, ",")) to split by comma and transpose to rows.
  • Can TEXTSPLIT create a 2D array from structured text? Yes! =TEXTSPLIT("a,b;c,d", ",", ";") creates a 2x2 array where commas separate columns and semicolons separate rows. Perfect for parsing simple table data from text.

Editorial review

  • Reviewed by Excel.Directory Editorial Team. Updated May 2026.

When to use TEXTSPLIT

  • Parse CSV data — common Text scenario for TEXTSPLIT.
  • Split addresses — common Text scenario for TEXTSPLIT.
  • Data transformation — common Text scenario for TEXTSPLIT.

TEXTSPLIT in the Text category

  • Browse all Text functions at /categories/text/ for related formulas.
  • TEXTSPLIT syntax: =TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with])
  • text (required): Text to split
  • col_delimiter (required): Delimiter for columns
  • row_delimiter (optional): Delimiter for rows
  • Confirm TEXTSPLIT arguments match the syntax shown above before filling down.
  • Lock table and range references with $ when copying formulas across rows or sheets.

Formula checklist before you copy down

  • Confirm TEXTSPLIT arguments match the syntax shown above before filling down.
  • Lock table and range references with $ when copying formulas across rows or sheets.
  • If results look wrong, check for text stored as numbers and invisible spaces with TRIM.
  • Spot-check three known input rows manually against expected output.

Related Excel functions

  • TEXTBEFORE (/functions/textbefore/): Returns text that occurs before a specified delimiter.
  • TEXTAFTER (/functions/textafter/): Returns text that occurs after a specified delimiter.
  • TEXTJOIN (/functions/textjoin/): Joins text from multiple ranges with a delimiter, with option to ignore empty cells.

Comparisons involving this function

  • LEFT vs MID vs RIGHT (/compare/left-vs-mid-vs-right/): Use LEFT, MID, and RIGHT for fixed-position extraction. Use TEXTBEFORE, TEXTAFTER, and TEXTSPLIT in modern Excel when values are separated by a delimiter.

Errors to watch for

  • Returns array - needs spill space — review causes on linked error pages in the directory.

Copy-paste audit workflow

  • Enter TEXTSPLIT on three test rows with known expected output documented on a QA tab.
  • Fill down only after absolute references are locked on lookup tables and rate tables.
  • Compare against manual calculation or a calculator for financial and statistical functions.
  • Search this directory for comparison guides when choosing between similar functions in the same category.

TEXTSPLIT worked examples to copy

  • =TEXTSPLIT("a,b,c", ",") — Split by comma. Expected result: a | b | c.
  • =TEXTSPLIT("a-b;c-d", "-", ";") — Split rows and cols. Expected result: 2x2 array.

TEXTSPLIT reference summary for crawlers and offline review

  • TEXTSPLIT belongs to the Text category in Excel. Splits text into rows or columns using delimiters.
  • Full syntax: =TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with]). Open /functions/textsplit/ for parameters, FAQs, and related pages.
  • Common mistakes: Returns array - needs spill space
  • Pair this function with comparison guides when another Excel formula might fit the same task better.
  • Review fix-excel-formula-errors when unexpected errors appear after upgrading Excel or sharing across locales.