Cellwise Start free

Cleanup function

Excel TEXTSPLIT function

TEXTSPLIT splits text at a delimiter and spills the pieces across columns, down rows, or both. It is a live formula version of Text to Columns: change the text and the pieces update.

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

Arguments

text
The text to split.
col_delimiter
Where to start a new column, such as "," or a list like {",",";"}. Leave it empty to split into rows only.
row_delimiter
Optional. Where to start a new row.
ignore_empty
Optional. TRUE skips empty pieces from two delimiters in a row. The default, FALSE, keeps them as empty cells.
match_mode
Optional. 0 matches the delimiter’s case exactly (default), 1 ignores case.
pad_with
Optional. What fills short rows in a two-way split. The default is #N/A.

TEXTSPLIT examples

Every result below was calculated from this sample sheet by the same spreadsheet engine the Cellwise app uses.

AB
1FieldText
2Order lineSKU-104|Desk lamp|2|24.50
3ContactOrtiz, Maya
4Tagsred;green,blue
5StockPaper=12;Toner=3;Pens=40

Download this sheet as an Excel practice file (.xlsx)

  1. =TEXTSPLIT(B2,"|")

    Result: spills 1 row × 4 columns

    SKU-104Desk lamp224.50

    Splits the order line at each | into four cells across.

  2. =TEXTSPLIT(B4,{",",";"})

    Result: spills 1 row × 3 columns

    redgreenblue

    A list of delimiters splits at any of them: the tags typed with a mix of ; and , become red, green and blue.

  3. =TEXTSPLIT(B5,"=",";")

    Result: spills 3 rows × 2 columns

    Paper12
    Toner3
    Pens40

    A column delimiter and a row delimiter together: the stock text becomes a table of three rows and two columns.

  4. =SUM(--INDEX(TEXTSPLIT(B5,"=",";"),0,2))

    Result: 55

    The pieces are text. INDEX takes the quantity column and -- turns it into numbers so SUM can add them: 55.

How to use TEXTSPLIT

  1. Click the top-left cell of an empty area.
  2. Type =TEXTSPLIT( and the cell with the text.
  3. Add a comma and the delimiter in quotes, such as ",".
  4. For rows, add the row delimiter as the third argument (leave the second empty to split into rows only).
  5. Close the bracket and press Enter.

Common TEXTSPLIT mistakes

Numbers that will not add up

Every piece is text, even "12", so SUM returns 0. Convert with -- or VALUE, as in the last example.

Empty cells from double delimiters

"a,,b" splits into a, an empty cell and b. Set ignore_empty to TRUE: =TEXTSPLIT(A2,",",,TRUE).

#N/A in a two-way split

Rows with fewer pieces are padded with #N/A. Give pad_with a value, such as "" as the sixth argument.

Using it in older Excel

TEXTSPLIT needs Microsoft 365, Excel 2024 or Excel for the web. Elsewhere use Data > Text to Columns, or LEFT, MID and FIND.

TEXTSPLIT FAQ

How do I split text into columns with a formula?

Use =TEXTSPLIT(B2,"|") with your delimiter. The pieces spill to the right, one per cell.

How do I split text into rows in Excel?

Leave the column delimiter empty and give a row delimiter: =TEXTSPLIT(B5,,";") spills Paper=12, Toner=3 and Pens=40 down a column.

How do I split by more than one delimiter?

Pass a list in braces: =TEXTSPLIT(B4,{",",";"," "}) splits at a comma, a semicolon or a space.

What is the difference between TEXTSPLIT and Text to Columns?

Text to Columns splits once and overwrites cells. TEXTSPLIT is a formula, so the result updates when the text changes and the original stays as it is.

Guides that use TEXTSPLIT

Related functions

Updated