SPLIT breaks a cell into pieces at a delimiter and spills them into the cells to the right. It looks simple, and with =SPLIT(A2, ",") on clean data it is. But three of its default settings changed our results in ways that are easy to miss: it splits at each character of the delimiter, it drops empty pieces, and it turns anything that looks like a number into a number.
Tested in Google Sheets. On November 11, 2026 we ran each formula on this page and recorded what came out, joining the pieces with a | so every space and empty piece was visible. How we test
The syntax
=SPLIT(text, delimiter, [split_by_each], [remove_empty_text])
- split_by_each (default TRUE): every character in the delimiter is a separate delimiter.
- remove_empty_text (default TRUE): empty pieces between two delimiters are dropped.
An empty delimiter is not allowed: =SPLIT(A2, "") returned #VALUE! with Function SPLIT parameter 2 value should be non-empty.
Split into columns
With red, green , blue in A2, the result depends on how you handle the spaces:
| Formula | Pieces |
|---|---|
=SPLIT(A2, ",") |
“red” | ” green ” | ” blue” (spaces kept) |
=SPLIT(A2, ", ") |
“red” | “green” | “blue” |
=SPLIT(A2, ", ", FALSE) |
“red” | “green ” | “blue” |
=ARRAYFORMULA(TRIM(SPLIT(A2, ","))) |
“red” | “green” | “blue” |
The leftover spaces matter: ” green” doesn’t match “green” in a lookup or a filter. The TRIM version is the safe choice, because it removes spaces on both sides of each piece whatever the original spacing.
Found in testing: ", " as the delimiter does not mean “comma followed by space”. By default SPLIT splits at every comma and every space. That happened to give clean results above, but on Mary-Ann Lee, Bob it split the name in two: “Mary-Ann” | “Lee” | “Bob”. With FALSE as the third argument, the delimiter is the whole “, ” and the result was “Mary-Ann Lee” | “Bob”.
Split into rows
SPLIT always spills across. To get the pieces down a column, wrap it in TRANSPOSE:
=TRANSPOSE(SPLIT(A2, ","))
With “red, green , blue” that gave 3 rows. Add TRIM as above if the pieces have spaces.
Split a whole column into one list
For a column of cells such as a,b,c and d,e, you want one list a, b, c, d, e. SPLIT on a range needs ARRAYFORMULA, and TOCOL then stacks the result into a single column:
=TOCOL(ARRAYFORMULA(SPLIT(A2:A3, ",")), 1)
That returned 5 rows: a, b, c, d, e. The 1 tells TOCOL to skip blanks, which matters because the shorter row leaves an empty cell in the grid SPLIT builds. FLATTEN, an older function, did the same job but kept that blank: 6 rows, the last one empty.
Found in testing: without ARRAYFORMULA, =TOCOL(SPLIT(A2:A3, ","), 1) failed with #VALUE! and the message The default output of this reference is a single cell in the same row but a matching value could not be found. The same happened with FLATTEN. The message itself suggests the fix: add ARRAYFORMULA.
Keep a label from another column
If each row has a name in column B and a list in column A, you usually want the name next to each piece. This version builds “name-piece” pairs and skips the blanks:
=TOCOL(ARRAYFORMULA(IF(SPLIT(A2:A3, ",")="", , B2:B3&"-"&SPLIT(A2:A3, ","))), 1)
With x and y as the names, it returned x-a, x-b, x-c, y-d, y-e. Split the pairs again at the hyphen if you need them in two columns.
Empty pieces
With a,,b, SPLIT returned 2 columns, a and b: the empty piece between the two commas disappeared. That shifts every later piece one column to the left, which breaks data where each position has a meaning (for example “name, middle name, surname”). Set the fourth argument to FALSE to keep the empty piece: =SPLIT(A2, ",", TRUE, FALSE) returned 3 columns.
The same setting applies to repeated spaces. =SPLIT("a b c", " ") gave 3 pieces; with FALSE as the fourth argument it gave 4, with an empty piece where the double space was.
Lost leading zeros
SPLIT converts pieces that look like numbers. =SPLIT("05,007", ",") returned the numbers 5 and 7, and the fourth argument didn’t change that. For codes, IDs or zip codes, that silently changes your data.
- When every code has the same length, format them back:
=ARRAYFORMULA(TEXT(SPLIT(A2, ","), "000"))returned “005” and “007”. Note that this pads “05” to three digits too. - To keep each piece exactly as written, add a letter before each piece, split, and remove the letter:
=ARRAYFORMULA(REGEXREPLACE(SPLIT(REGEXREPLACE(A2, "([^,]+)", "x$1"), ","), "^x", ""))returned “05” and “007” as text. - For a single piece, REGEXEXTRACT keeps the text:
=REGEXEXTRACT(A2, "[^,]+$")returned “007”.
Pieces like 2026 or 1.5 also came out as numbers, which is usually what you want. Pieces like 1/2 stayed as text in our test.
Line breaks and other special delimiters
To split a cell with several lines, use CHAR(10) as the delimiter: =SPLIT(A2, CHAR(10)) gave 2 pieces from a two-line cell. A dot, a pipe and a semicolon worked as plain delimiters, with no escaping.
Related guides
- Extract text before or after a character
- Remove extra spaces when TRIM doesn’t work
- Convert text to numbers
- UNIQUE without blanks (remove duplicates from the split list)