How to Use SPLIT in Google Sheets: Comprehensive Guide

SPLIT divides text at a delimiter and spreads the pieces across cells. It turns "Ana, Sales, Paris" in one cell into three cells, a full name into first and last, or a URL into its parts, which makes it the first stop for cleaning imported data.

Syntax

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

text: The text to divide.
delimiter: The character or string to split on.
split_by_each [Optional]: TRUE (default) treats each character in delimiter as a separate delimiter. FALSE treats the whole delimiter as one unit.
remove_empty_text [Optional]: TRUE (default) drops empty pieces. FALSE keeps them as empty cells.

The result spills to the right, so the cells to the right of the formula must be empty.

Basic examples

=SPLIT("Ana,Sales,Paris", ",")

Returns three cells: Ana, Sales, Paris.

=SPLIT(A2, " ")

Splits on spaces, so Jane Doe becomes Jane and Doe.

The split_by_each trap

With the default split_by_each = TRUE, every character in the delimiter splits the text on its own. This surprises people who use a multi-character delimiter:

=SPLIT("Ana, Sales, Paris", ", ")

Because both , and the space are delimiters, this returns Ana, Sales, Paris (the empty pieces are dropped), which works here by luck. But with text that contains spaces inside items:

=SPLIT("New York, Los Angeles", ", ")

returns New, York, Los, Angeles: four pieces, not two. To treat ", " as a single delimiter, set the third argument to FALSE:

=SPLIT("New York, Los Angeles", ", ", FALSE)

Returns New York and Los Angeles.

Empty pieces

By default, empty pieces are removed, so consecutive delimiters collapse. For "a,,c":

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

returns a, c. To keep the gap, set the fourth argument to FALSE:

=SPLIT("a,,c", ",", TRUE, FALSE)

returns a, an empty cell, c. Keep the position of fields when the data has missing values, such as CSV rows.

Practical patterns

Split a whole column

=ARRAYFORMULA(SPLIT(A2:A, ","))

Each row is split into its own row of cells.

Get only the nth piece

=INDEX(SPLIT(A2, " "), 1, 2)

Returns the second word. The INDEX picks row 1, column 2 of the result. To get the last piece regardless of how many there are:

=INDEX(SPLIT(A2, " "), 1, COUNTA(SPLIT(A2, " ")))

First and last name

=INDEX(SPLIT(A2, " "), 1, 1)

=REGEXEXTRACT(A2, "\S+$")

Use REGEXEXTRACT for the last word since it works for any word count.

Turn the pieces into a column

SPLIT spills to the right. To get a vertical list, wrap it in TRANSPOSE:

=TRANSPOSE(SPLIT(A2, ","))

Split and trim

Items such as "a , b" leave spaces around the pieces:

=ARRAYFORMULA(TRIM(SPLIT(A2, ",")))

Split on several delimiters at once

With split_by_each on, list all the delimiters as characters:

=SPLIT(A2, ",;|")

This splits on a comma, a semicolon or a pipe.

Split on line breaks

=SPLIT(A2, CHAR(10))

Split a URL or path into parts

=SPLIT("example.com/blog/post-1", "/")

Returns example.com, blog, post-1.

Count items in a cell

=COUNTA(SPLIT(A2, ","))

Split into text only

SPLIT converts pieces that look like numbers or dates into actual numbers and dates, which can strip leading zeros ("007" becomes 7). If you need to keep text, format the result cells as plain text first, or rebuild with TO_TEXT where needed.

Common errors

#REF!: "Result was not automatically expanded, please insert more columns" or "Array result was not expanded because it would overwrite data". There is something in the cells to the right. Clear them.
Unexpected extra pieces. split_by_each is splitting on every character of a multi-character delimiter. Set the third argument to FALSE.
Leading zeros lost. The pieces were converted to numbers. Use plain text formatting.
Spaces around the pieces. Wrap in TRIM.

SPLIT vs. REGEXEXTRACT vs. LEFT/MID

Use SPLIT when a clear delimiter separates the parts. Use REGEXEXTRACT when you want a piece defined by its shape rather than a delimiter. Use LEFT, MID and RIGHT for fixed positions. To go the other way and join pieces, use TEXTJOIN.

Frequently asked questions

Can I split into rows instead of columns? Yes, wrap in TRANSPOSE.

Can I split on a word like "and"? Yes. Use SPLIT(A2, " and ", FALSE), which treats the whole string as one delimiter.

Is SPLIT case-sensitive? Yes, for letters in the delimiter.

Is there a limit on pieces? Only the sheet's column limit.

Related Functions

TEXTJOIN: Join text with a delimiter, the reverse of SPLIT.
REGEXEXTRACT: Extract text by pattern.
TRANSPOSE: Swap rows and columns.
TRIM: Remove extra spaces.
ARRAYFORMULA: Apply a formula to a whole range.

SPLIT