How to Split Text Into Columns in Google Sheets

Paste =SPLIT(A2," ") to break the text in A2 into separate columns at every space — “Jane Doe” becomes Jane in one cell and Doe in the next.

=SPLIT(A2," ")

Splits A2 wherever a space appears and spills the pieces across the columns to the right. Leave those columns empty.

How it works

SPLIT breaks one string into parts using a delimiter you choose — a space, comma, dash, anything in quotes. The result spills into the cells to the right, so make sure they are clear. Unlike the Data → Split text to columns menu action, the formula keeps the original cell intact and updates live. To process a whole column at once, wrap it in ARRAYFORMULA.

Variations

Split on commas

=SPLIT(A2,",")

Good for CSV-style values pasted into one cell.

Split and keep empty cells

=SPLIT(A2,",",TRUE,FALSE)

The final FALSE stops it from collapsing consecutive delimiters.

Split on either a comma or a space

=SPLIT(A2,", ")

Each character in the delimiter string is treated as a separator.

Examples

Scenario	Formula
Full name into first / last	`=SPLIT(A2," ")`
Tag list separated by commas	`=SPLIT(A2,",")`

FAQ

Why do I get a #REF! error when I use SPLIT?

The result needs empty cells to the right to spill into. #REF! means it is overwriting existing data — clear those columns or move the formula.

How do I split a whole column at once?

Use ARRAYFORMULA around SPLIT is limited; instead drag the formula down, or use =ARRAYFORMULA(SPLIT(A2:A," ")) which works for a single delimiter per row.

How is SPLIT different from the menu split?

The menu (Data → Split text to columns) overwrites the original cell one time. =SPLIT keeps the source and recalculates when it changes.

Related formulas

→ See all 34 Google Sheets formulas in one cheat sheet