Spreadsheet Practice

How to split text into columns with a formula in Excel

Excel formula

=TEXTSPLIT(A2,", ")

In Excel 365, =TEXTSPLIT(A2,", ") spills each part into its own column. In Google Sheets use =SPLIT(A2,", ",FALSE).

Result on the example
one result per row in B2, filled down to B6
Works in
Excel 2024 and Microsoft 365
Google Sheets
Different formula, 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

TEXTSPLIT breaks the text at every delimiter and spills the pieces to the right, one per column, so one formula per row fills B, C and D. Unlike Text to Columns, it updates when the address changes.

Google Sheets' SPLIT splits on each character of the delimiter by default, which would also split at every space. The FALSE makes it treat ", " as one delimiter.

=TEXTSPLIT(A2,", ")

Result{1 row × 3 cols} "1420 Lake St", "Minneapolis", "MN"

  1. A2"1420 Lake St, Minneapolis, MN"

    The value in A2.

  2. TEXTSPLIT(A2,", "){1 row × 3 cols} "1420 Lake St", "Minneapolis", "MN"

    TEXTSPLIT: Splits text across columns, and rows, at the delimiters you give.

    • textA2"1420 Lake St, Minneapolis, MN"
    • col_delimiter", "

In Google Sheets

Google Sheets needs a different formula: =SPLIT(A2,", ",FALSE). On the same data it gives the same result. Here it is running in Google Sheets mode.

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=TRIM(MID(SUBSTITUTE($A2,",",REPT(" ",100)),(COLUMNS($B2:B2)-1)*100+1,100))1420 Lake St, 88 Oak Ave, 7 Elm Ct…Works in every version: fill it right to D and down. COLUMNS($B2:B2) is 1, 2, 3 as you fill right, so each column picks the next 100-character block.

Common mistakes

  • TEXTSPLIT needs empty cells to spill into. Anything in C or D gives #SPILL!.
  • In Google Sheets, SPLIT(A2,", ") without FALSE splits on the comma and on every space, giving "1420", "Lake", "St"…
  • SPLIT in Google Sheets turns number-like parts into numbers (a ZIP code loses its leading zero). TEXTSPLIT keeps them as text.

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 with a formula

In B2, split the address at each ", " so the street, city and state land in columns B, C and D. Fill down to B6.

Questions

How do I split into rows instead of columns?

Use the row delimiter, the third argument: =TEXTSPLIT(A2,,", ").

How do I split on more than one delimiter?

Give TEXTSPLIT a list: =TEXTSPLIT(A2,{",",";"}).

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.