SPLIT function in Google Sheets
SPLIT breaks text into pieces at a delimiter and puts each piece in its own cell to the right. By default it splits at every character in the delimiter and drops empty pieces, and pieces that look like numbers come back as numbers.
- Works in
- Google Sheets only
- Returns
- A row of values, one cell per piece. Pieces that look like numbers become numbers.
Syntax
=SPLIT(text, delimiter, [split_by_each], [remove_empty_text])
| Argument | Required? | What it is |
|---|---|---|
text | Required | The text to split, usually a cell. |
delimiter | Required | The character or characters to split at, such as "," or " ". |
split_by_each | Optional | Optional. TRUE (the default) splits at each character of delimiter separately. FALSE treats the whole delimiter as one unit, such as ", " or " and ". |
remove_empty_text | Optional | Optional. TRUE (the default) drops empty pieces, such as the gap between two commas in a row. FALSE keeps them as empty cells. |
Examples
Each result is calculated from the sample sheet by our formula engine in Sheets mode, the same one that grades the practice.
| Cell | Formula | Result | What it shows |
|---|---|---|---|
| C2 | =SPLIT(A2," ") | Anaspills 1 × 2 | Splits at the space: Ana in C2, Lima in D2 |
| C3 | =SPLIT(A3,", ") | Sanspills 1 × 4 | Each character is a delimiter by default, so the space inside San Jose splits too: San, Jose, CA, 95112 |
| C4 | =SPLIT(A4,", ",FALSE) | San Josespills 1 × 3 | FALSE treats ", " as one delimiter, so San Jose stays whole: San Jose, CA, 95112 |
Try it live
This is the sample sheet. Change any value or edit a formula, and the results update.
Practice SPLIT
Graded instantly in Sheets mode, then re-checked on changed data.
First and last names
B2, split the contact name in A2 into a first name and a last name, one per column. Fill the formula down to B6.Common mistakes
| What you see | Why, and how to fix it |
|---|---|
| Text splits in too many places | SPLIT treats each character of the delimiter as its own delimiter, so ", " splits at commas and at every space. Set split_by_each to FALSE: =SPLIT(A2,", ",FALSE). |
| Leading zeros disappear | SPLIT turns a piece like 00742 into the number 742. To keep the zeros, pull that piece out with REGEXEXTRACT, which always returns text. |
| An empty column you expected is missing | remove_empty_text is TRUE by default, so "a,,c" gives two pieces, not three. Keep the gap with =SPLIT(A2,",",TRUE,FALSE). |
| #REF! with "Array result was not expanded" | The cells to the right aren't empty. SPLIT needs one empty cell for each piece. |
Tips
- Pick one piece with INDEX: =INDEX(SPLIT(A2," "),1,2) returns the second word.
- For a one-time split that doesn't update, use Data > Split text to columns.
SPLIT in Excel
Excel has no SPLIT. In Excel 2024 and Microsoft 365, TEXTSPLIT does the same job; older versions use Data > Text to Columns, or LEFT, MID and FIND.
Questions
How do I split text into columns in Google Sheets?
Use =SPLIT(A2,","). The pieces fill the cells to the right and update when A2 changes. Data > Split text to columns does the same once, without a formula.
How do I split at a word instead of a character?
Set split_by_each to FALSE: =SPLIT(A2," and ",FALSE) splits only where the whole " and " appears.
What is the Excel equivalent of SPLIT?
TEXTSPLIT in Excel 2024 and Microsoft 365. It treats the delimiter as one unit by default, the opposite of SPLIT.
Last reviewed October 2026. Every example result on this page is calculated live by the Spreadsheet Practice formula engine in Sheets mode.