Spreadsheet Practice

How to split text into columns in Google Sheets

Google Sheets formula

=SPLIT(A2,", ",FALSE)

Use SPLIT with the separator: =SPLIT(A2,", ",FALSE) puts each part of A2 in its own column. FALSE makes ", " count as one separator.

Result on the example
one result per row in B2, filled down to B6
Excel
See below

See it work

The formula is already in B2, filled down to B6. Change any input and the result updates, or edit the formula to experiment.

How it works

SPLIT breaks the text at the separator and spills the pieces to the right, so B2 holds the city and C2 and D2 fill in by themselves. By default SPLIT treats every character of the separator as a separator of its own, which would also split at each space; FALSE as the third argument makes it split only at the full ", ".

=SPLIT(A2,", ",FALSE)

Result{1 row × 3 cols} "Austin", "TX", 78701

  1. A2"Austin, TX, 78701"

    The value in A2.

  2. SPLIT(A2,", ",FALSE){1 row × 3 cols} "Austin", "TX", 78701

    SPLIT: Splits text across columns at a delimiter.

    • textA2"Austin, TX, 78701"
    • delimiter", "
    • split_by_eachFALSE

In Excel

In Excel 2024 or Microsoft 365, use TEXTSPLIT: =TEXTSPLIT(A2,", "). In older versions, use Data > Text to Columns.

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=TEXTSPLIT(A2,", ")Excel onlyAustin, Denver, Boston…The Excel version. TEXTSPLIT treats the whole separator as one unit by default.

Common mistakes

  • SPLIT turns pieces that look like numbers into numbers, so Boston's ZIP 02108 becomes 2108. Pull such pieces out with REGEXEXTRACT, which keeps text: =REGEXEXTRACT(A2,"\d{5}$").
  • Without FALSE, SPLIT(A2,", ") also splits "San Jose" into two cells.
  • The cells to the right must be empty, or SPLIT shows #REF!.

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Split text into columns

In B2, split the address in A2 into city, state and ZIP, one per column, then fill the formula down to B6.

Questions

How do I split a whole column with one formula?

Wrap it in ARRAYFORMULA: =ARRAYFORMULA(SPLIT(A2:A6,", ",FALSE)).

How do I split without a formula?

Select the column and use Data > Split text to columns. The result doesn't update.

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine.