SPLIT Function in Excel, Google Sheets, Airtable & Notion
SPLIT breaks text into pieces at a delimiter and spills each piece into its own cell. SPLIT("red, green, blue", ", ") fills three cells with “red”, “green” and “blue”.
Syntax in each app
| App | Support | Syntax & notes |
|---|---|---|
| Excel | ✔ Built in | TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with])Excel 365 / Excel 2024 Excel has no SPLIT; TEXTSPLIT does the same job. Set ignore_empty to TRUE to drop empty pieces, as Sheets does by default. Older Excel: Data → Text to Columns. |
| Google Sheets | ✔ Built in | SPLIT(text, delimiter, [split_by_each], [remove_empty_text])split_by_each defaults to TRUE: every character of the delimiter is a separate split point. Pass FALSE to split on the whole delimiter. |
| Airtable | ✘ Not available | — No SPLIT and no way to spill values into several fields. Pull out one piece at a time, e.g. the first item with REGEX_EXTRACT({Tags}, "[^,]+") or the part before a comma with LEFT({Tags}, FIND(",", {Tags}) - 1). For real lists, use a multiple-select or linked-record field. |
| Notion | ✔ Built in | split(text, separator)Returns a list. Use .at(0) for the first item (0-based), .length() to count items, and .filter(current != "") to drop empty items. |
| Excel | =TEXTSPLIT("red, green, blue", ", ", , TRUE) | open excel.new ↗ |
|---|---|---|
| Google Sheets | =SPLIT("red, green, blue", ", ", FALSE) | open sheets.new ↗ |
| Airtable | ✘ No equivalent. No SPLIT and no way to spill values into several fields. Pull out one piece at a time, e.g. the first item with REGEX_EXTRACT({Tags}, "[^,]+") or the part before a comma with LEFT({Tags}, FIND(",", {Tags}) - 1). For real lists, use a multiple-select or linked-record field. | |
| Notion | split("red, green, blue", ", ").filter(current != "") | |
Excel & Sheets use cell ranges. Paste this sample data into cell A1 of a blank sheet, then paste the formula into any empty cell.
The tester always splits on the whole delimiter, so the Google Sheets formula adds FALSE for split_by_each when the delimiter is longer than one character.
Arguments
| Argument | What it means |
|---|---|
text | The text to split. |
delimiter | Where to split, e.g. "," or ", ". |
[split_by_each] optional | Sheets only. TRUE (default) splits on each character of the delimiter; FALSE on the whole delimiter. |
[remove_empty_text] optional | Sheets: TRUE (default) drops empty pieces. Excel TEXTSPLIT's ignore_empty does the same but defaults to FALSE. |
Examples
| Formula | Result | Why | Copy |
|---|---|---|---|
=SPLIT("red, green, blue", ", ", FALSE) | red | green | blue | Sheets: split on the whole “, ”. | |
=TEXTSPLIT("red, green, blue", ", ") | red | green | blue | Excel 365 equivalent. | |
=SPLIT("a-b c", "- ") | a | b | c | Sheets default: each character (dash and space) is a separator. | |
=INDEX(SPLIT(A2, " "), 1, 2) | Second word | Wrap in INDEX to take a single piece. |
Examples use Excel / Google Sheets syntax unless noted.
How SPLIT works
SPLIT is the formula version of Text to Columns: it turns “Smith, John” or a comma-separated tag list into separate cells that update when the source changes. The result spills to the right, so leave empty cells for it, or you'll get a #REF! / #SPILL! error. Wrap it in INDEX to pick one piece, or use TRANSPOSE to spill downwards.
The big gotcha in Google Sheets is split_by_each, which defaults to TRUE. SPLIT(A2, ", ") does not split on “comma-space”. It splits on every comma and every space, so “New York, Boston” becomes three cells. Pass FALSE as the third argument to treat the delimiter as one unit. Sheets also turns pieces that look like numbers or dates into numbers. Excel's TEXTSPLIT (Microsoft 365) always splits on the whole delimiter, keeps the pieces as text, and keeps empty pieces unless ignore_empty is TRUE. It can also split into rows with its third argument.
Notion's split(text, separator) returns a list rather than cells, which you can process further with .map(), .filter(), .at() and .join(). Airtable can't split text into several values in a formula; extract individual pieces with REGEX_EXTRACT or LEFT/MID/FIND, or store the data as a multiple-select field.
Frequently Asked Questions
- Why does SPLIT give me more columns than expected?
- In Google Sheets, each character of the delimiter is used separately by default. Use
=SPLIT(A2, ", ", FALSE)to split only on the full “, ”. - How do I split text in older Excel?
- Use Data → Text to Columns, or formulas with LEFT, MID and FIND for each piece.