SPLIT breaks one piece of text into several cells wherever a delimiter appears: names into first and last, product codes into their parts, comma-separated tags into separate columns. It is the formula version of Data › Split text to columns, and unlike that menu it updates when the text changes.
The character or characters to split at, in quotes, e.g. " " or "-".
split_by_each
Optional, TRUE by default. TRUE treats every character of the delimiter as its own delimiter. FALSE splits only where the whole delimiter text appears.
remove_empty_text
Optional, TRUE by default. TRUE drops the empty pieces you get between two delimiters in a row; FALSE keeps them as blank cells.
Practice SPLIT
Task 1 of 4
Split the full name in A2 into first and last name. The formula goes in B2 and spills into C2.
B2
fx
Tip: while typing a formula, click a cell or drag across cells to insert references. Press Enter to check, and Tab to accept a function suggestion.
SPLIT examples
Each example below runs against this sample sheet. The results are calculated by the same engine as the practice grid.
A
B
C
D
1
Full name
SKU
Route
Tags
2
Maya Patel
ELC-0427-NY
Boston to Denver
urgent,,client,q3
3
Leo Rossi
FUR-1180-TX
Miami to Chicago
design,launch
4
Ava Kim
OFC-0093-CA
Seattle to Portland
billing,,review
Names, product codes, routes and tags to split
Formula
Result
What it shows
=SPLIT(A2, " ")
Maya
Patel
First and last name into two cells.
=SPLIT(B2, "-")
ELC
427
NY
A product code into three parts. "0427" comes back as the number 427, without its leading zero.
=SPLIT(C2, " to ")
B
s
n
Denver
The classic surprise: every space, "t" and "o" counts as a delimiter, so "Boston" is chopped up.
=SPLIT(C2, " to ", FALSE)
Boston
Denver
split_by_each set to FALSE splits only at the exact text " to ".
=SPLIT(D2, ",")
urgent
client
q3
The empty piece between the two commas is dropped.
=SPLIT(D2, ",", TRUE, FALSE)
urgent
client
q3
remove_empty_text set to FALSE keeps it as a blank cell.
=ARRAYFORMULA(SPLIT(A2:A4, " "))
Maya
Patel
Leo
Rossi
Ava
Kim
Every name in the column at once, one row per name.
Splitting a whole column
SPLIT works on one piece of text at a time. To split a whole column with a single formula, give it the range and wrap it in ARRAYFORMULA: =ARRAYFORMULA(SPLIT(A2:A100, ",")). Each row's pieces spill across, and new rows are covered as long as they fall inside the range.
SPLIT vs Split text to columns
The Data › Split text to columns menu does the same job once and overwrites the cells next to your data. SPLIT keeps the original text, leaves the result in a separate place, and recalculates when the source changes. Use the menu for a one-off cleanup, and SPLIT when the data keeps arriving.
Tips and common mistakes
The results spill to the right. If any cell in the way already holds something, SPLIT shows #REF! until you clear it.
Pieces that look like numbers or dates are converted, so ZIP codes, IDs and codes with leading zeros lose them. Keep them as text by splitting with REGEXEXTRACT instead, or rebuild them with TEXT(value, "0000").
Splitting at ", " with the default settings splits at every comma and every space, which also breaks apart multi-word values. Use split_by_each FALSE when the delimiter is more than one character.
To get just one piece, wrap SPLIT in INDEX: INDEX(SPLIT(A2, " "), 1, 2) returns the second word.