SPLIT pitfall 1: By default, EACH character in the delimiter string is treated as a separator: SPLIT("a,b;c", ",;") produces three pieces, not two?

By default, EACH character in the delimiter string is treated as a separator: `SPLIT("a,b;c", ",;")` produces three pieces, not two. Pass FALSE as the 3rd arg to use the whole string as the delimiter.

SPLIT pitfall 2: Empty results are removed by default?

Empty results are removed by default. Pass FALSE as the 4th arg to keep empty splits (useful for CSV-shaped data with missing fields).

SPLIT pitfall 3: Returns an array that auto-spills horizontally?

Returns an array that auto-spills horizontally. To split into rows instead, transpose: `=TRANSPOSE(SPLIT(...))`.

Home/Formulas/Text/SPLIT

Texteasy

SPLIT()

Divides text around a specified character or string.

Syntax

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

Example

=SPLIT("a,b,c", ",")

Paste this into Google Sheets to run it. The live playground on this site covers 24 of the 61 functions, and SPLIT isn't one of them yet.

Common pitfalls

By default, EACH character in the delimiter string is treated as a separator: SPLIT("a,b;c", ",;") produces three pieces, not two. Pass FALSE as the 3rd arg to use the whole string as the delimiter.
Empty results are removed by default. Pass FALSE as the 4th arg to keep empty splits (useful for CSV-shaped data with missing fields).
Returns an array that auto-spills horizontally. To split into rows instead, transpose: =TRANSPOSE(SPLIT(...)).

Related formulas

TEXTJOIN()

Combines text with a delimiter, optionally skipping empty cells.

Used by

TEXTJOIN()

Combines text with a delimiter, optionally skipping empty cells.

More Text formulas →Keyboard shortcuts for Sheets →