How to Split Text into Columns in Google Sheets
Google Sheets offers two main ways to break a single text string into separate columns: the SPLIT function (a formula) and the Text to Columns wizard (a menu command). Use the formula when you need a dynamic, repeatable solution; use the wizard for a quick, one‑time split.
1. Using the SPLIT Function
The SPLIT function takes a text value and a delimiter, then spills the pieces into adjacent cells.
Syntax
SPLIT(text, delimiter, [split_by_each], [remove_empty_text])
| Argument | Description |
|---|---|
| text | The string to split (e.g., a cell reference like A2). |
| delimiter | One or more characters that mark where to cut the string. |
| split_by_each (optional) | TRUE (default) splits on any character in delimiter; FALSE treats the whole delimiter as a single string. |
| remove_empty_text (optional) | TRUE (default) discards empty results; FALSE keeps them as blank cells. |
Basic example
| A | |
|---|---|
| A2 | John,Smith,35,New York |
=SPLIT(A2, ",")
Result (spills to the right):
| B | C | D | E |
|---|---|---|---|
| John | Smith | 35 | New York |
Multiple delimiters
If a column uses more than one separator (e.g., commas or spaces), list the characters together. The default split_by_each = TRUE treats each character separately.
| A |
|---|
apple; banana, cherry|date |
=SPLIT(A2, ";,| ", TRUE, TRUE)
Result:
| B | C | D | E |
|---|---|---|---|
| apple | banana | cherry | date |
Keeping empty fields
When two delimiters appear consecutively, the default behavior removes the empty entry. Set remove_empty_text to FALSE to retain a blank column.
| A |
|---|
2021||Approved |
=SPLIT(A2, "|", TRUE, FALSE)
Result:
| B | C | D |
|---|---|---|
| 2021 | (blank) | Approved |
Splitting with a regular‑expression delimiter
For more complex cases—e.g., commas, semicolons, or pipes and optional spaces—list every delimiter character in the second argument. With split_by_each set to TRUE, SPLIT treats each character as its own delimiter (SPLIT does not accept regex patterns).
| A (raw) |
|---|
Apple, Banana; Cherry|Date Fig |
=SPLIT(A2, ",;| ", TRUE, TRUE)
Result:
| B | C | D | E | F |
|---|---|---|---|---|
| Apple | Banana | Cherry | Date | Fig |
2. Text to Columns Wizard
The built‑in Data → Split text to columns tool lets you split a column without writing a formula. It’s handy for a one‑off operation.
| Step | Action |
|---|---|
| 1 | Select the cells containing the text you want to split. |
| 2 | Choose Data → Split text to columns. |
| 3 | A toolbar appears below the selection. Click the Separator dropdown. |
| 4 | Choose a preset separator (Comma, Semicolon, Space, etc.) or select Custom and type your own character(s). |
| 5 | The data instantly spills into adjacent columns. Adjust the separator again if needed. |
Tips for reliable results
- Trim extra spaces – After splitting, run Data → Clean up → Trim whitespace to remove leading/trailing spaces.
- Leave room – Ensure there are enough empty columns to the right; otherwise the split will overwrite existing data.
- Preserve source – Copy the original column to another sheet or column before using the wizard if you need to keep the raw text.
The wizard works in all current versions of Google Sheets (desktop and mobile).
3. Cleaning Up Split Results
Splitting often leaves stray spaces, line‑breaks, or non‑printing characters that can break downstream formulas.
| Goal | Formula (Google Sheets) | Excel equivalent |
|---|---|---|
| Remove leading/trailing spaces | =TRIM(A2) |
=TRIM(A2) |
| Collapse multiple internal spaces | =REGEXREPLACE(A2, "\s+", " ") |
=TRIM(A2) (TRIM also collapses repeated inner spaces) |
| Remove line‑breaks | =SUBSTITUTE(SUBSTITUTE(A2, CHAR(10), ""), CHAR(13), "") |
Same |
| Strip non‑printing characters | =CLEAN(A2) |
Same |
| All steps in one cell | =TRIM(REGEXREPLACE(CLEAN(SUBSTITUTE(A2, CHAR(10), "")), "\s+", " ")) |
Works in Excel 365/2021 (supports REGEXREPLACE); older Excel needs nested SUBSTITUTE calls. |
Quick workflow
- Split with
SPLIT(or the wizard). - Clean the resulting columns using
TRIM+REGEXREPLACEas shown above.
4. Common Pitfalls & Fixes
| Problem | Why it Happens | Quick Fix |
|---|---|---|
| Extra spaces after splitting | Delimiter does not include surrounding spaces. | Add a space to the delimiter list or wrap the source with TRIM: =ARRAYFORMULA(TRIM(SPLIT(A2, ","))). |
| Too many columns because a delimiter appears inside quoted text | SPLIT ignores quotation marks. |
Replace delimiters inside quotes with a temporary character, split, then restore (advanced). |
| Blank columns appear from consecutive delimiters | Default remove_empty_text = TRUE removes them, but you may have set it to FALSE. |
Use FILTER to drop empties: =FILTER(SPLIT(A2, ","), SPLIT(A2, ",") <> ""). |
#VALUE! error |
Delimiter is empty or the source cell contains an error. | Verify the delimiter string and wrap the formula in IFERROR: =IFERROR(SPLIT(A2, ","), ""). |
| Formula overwrites its own spill range when dragged down | SPLIT is array‑enabled; copying the formula creates overlapping spills. |
Place the formula in a single cell (e.g., B2) and let it spill, or use ARRAYFORMULA for whole columns: =ARRAYFORMULA(IF(A2:A="",, SPLIT(A2:A, ","))). |
5. FAQ
What is the SPLIT function and how does it work?
SPLIT takes a text string and a delimiter, then returns each piece in separate cells that spill to the right. Optional arguments let you treat the delimiter as a whole string and decide whether to keep empty results.
Can I split text using a space or comma as a delimiter?
Yes. Use =SPLIT(A2, ",") for commas, =SPLIT(A2, " ") for spaces, or combine them (=SPLIT(A2, ", ")) to handle both at once.
How do I keep the original data intact while splitting?
Place the SPLIT formula in a different column (e.g., start in B2) so the source column remains unchanged. With the wizard, copy the original column to another location before running Split text to columns.
What if my data contains multiple consecutive delimiters?
Set the fourth argument remove_empty_text to FALSE to retain blank cells (=SPLIT(A2, ",", TRUE, FALSE)). If you prefer to discard the blanks, leave the default (TRUE) or filter them afterward.
Is there a way to split text automatically when new data is added?
Yes. Wrap SPLIT in ARRAYFORMULA so it processes an entire column: =ARRAYFORMULA(IF(A2:A="",, SPLIT(A2:A, ","))). The formula updates automatically as rows are added.
With these tools and tips, you can reliably turn any delimited string into tidy, usable columns in Google Sheets.