# SPLIT function in Google Sheets

> SPLIT breaks text into pieces at a delimiter and puts each piece in its own cell to the right.

Source: https://spreadsheetpractice.com/google-sheets/functions/split · Updated October 28, 2026 · Formulas and results on this page were run by the Spreadsheet Practice formula engine on November 20, 2026

## Quick answer

- **What it does:** SPLIT breaks text into pieces at a delimiter and puts each piece in its own cell to the right.
- **Syntax:** `=SPLIT(text, delimiter, [split_by_each], [remove_empty_text])`
- **Most common use:** `=SPLIT(A2," ")` returns Ana (Splits at the space: Ana in C2, Lima in D2)
- **Works in:** Google Sheets only
- **Returns:** A row of values, one cell per piece. Pieces that look like numbers become numbers.

## What it does

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.

## 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 was calculated by running the formula on the sample sheet in Google Sheets mode.

| Cell | Formula | Result | What it shows |
|---|---|---|---|
| C2 | `=SPLIT(A2," ")` | Ana (spills 1 × 2) | Splits at the space: Ana in C2, Lima in D2 |
| C3 | `=SPLIT(A3,", ")` | San (spills 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 Jose (spills 1 × 3) | FALSE treats ", " as one delimiter, so San Jose stays whole: San Jose, CA, 95112 |

### The sample sheet

Every formula above runs on this data:

|  | A | B | C | D | E | F |
|---|---|---|---|---|---|---|
| 1 | Text |  | Pieces |  |  |  |
| 2 | Ana Lima |  | Ana | Lima |  |  |
| 3 | San Jose, CA, 95112 |  | San | Jose | CA | 95112 |
| 4 | San Jose, CA, 95112 |  | San Jose | CA | 95112 |  |

## 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.

---

Spreadsheet Practice (https://spreadsheetpractice.com) teaches spreadsheet formulas by practice: every exercise is graded in a live grid and re-checked on changed data.
