SheetsdrillSPLIT
SPLIT(text, delimiter, [split_by_each], [remove_empty_text]) breaks a text cell into pieces at each delimiter and places them in adjacent cells to the right. By default every character in the delimiter counts as a separate split point, so SPLIT(A1, ", ") splits on commas and on spaces.
Syntax
=SPLIT(text, delimiter, [split_by_each], [remove_empty_text])| Part | What it is |
|---|---|
text | The cell or string to split. |
delimiter | The character or characters to split at, for example "," or " " or "-". |
split_by_each | Optional, default TRUE: each character of delimiter splits separately. FALSE treats the delimiter as one whole string. |
remove_empty_text | Optional, default TRUE: consecutive delimiters do not create empty cells. |
Examples
=SPLIT("Ana Lopez", " ")Result: Ana | Lopez (in two cells)
=INDEX(SPLIT("Ana Lopez", " "), 1)Result: Ana
INDEX picks one piece so only one cell is used.
=SPLIT("red, green, blue", ", ", FALSE)Result: red | green | blue
With FALSE the two-character delimiter is matched as a whole.
Common mistakes
- #REF! because the pieces would overwrite cells to the right; clear them or wrap SPLIT in INDEX to take one piece.
- Splitting on ", " produces extra empty-looking columns or breaks words with spaces; pass FALSE as the third argument so the delimiter is treated as one string.
- Leading zeros disappear from pieces like 007 because SPLIT converts numeric text to numbers; use REGEXEXTRACT or TO_TEXT.
Practice SPLIT
These drills check your answer on live data, free, no account needed.
- First name with SPLITNames are typed as "Last, First". Return just the first name in B2 and B3 using SPLIT.
Questions
How do I get the last piece after splitting?
=INDEX(SPLIT(A1, " "), COUNTA(SPLIT(A1, " "))) returns the last word, or use REGEXEXTRACT(A1, "\S+$").
How do I split a whole column at once?
=ARRAYFORMULA(SPLIT(A2:A100, ",")) splits every row, or use Data > Split text to columns for a one-time split.
Related
- JOIN and TEXTJOINHow JOIN and TEXTJOIN in Google Sheets combine many cells into one string with a separator, skip blanks and join only matching rows, with common mistakes.
- LEFT, RIGHT and MIDHow LEFT, RIGHT and MID in Google Sheets extract characters from the start, end or middle of a cell, with FIND for variable positions, and common errors.
- REGEXMATCH, REGEXEXTRACT and REGEXREPLACEHow the regex functions in Google Sheets work: test a pattern with REGEXMATCH, pull text out with REGEXEXTRACT and clean it with REGEXREPLACE, with examples.
- TRIMHow TRIM in Google Sheets removes leading, trailing and repeated spaces, how to clean a whole column, and how to clear non-breaking spaces and line breaks.