Split Text into Columns by a Delimiter

Excel & Google Sheets

How it works

This is a case where the two platforms genuinely use different functions for the same job. Excel 365's TEXTSPLIT(A2,",") breaks the text at every comma and spills each piece into its own column to the right. Google Sheets' SPLIT(A2,",") does the same — with one quirk: SPLIT treats EACH character of the delimiter as a separate split point unless you add FALSE as a third argument, so SPLIT(A2,", ") splits on commas AND spaces. Both are formulas, so unlike the manual Text to Columns tool, the results update automatically when the source changes. Neither exists in Excel 2019 or older — see the mistakes for the fallback.

A2

The cell holding the combined text.

","

The delimiter to split at. Use ";", "|", or " " for other separators.

spilled result

Each piece lands in its own column automatically — leave the cells to the right empty.

When to use it

Use this on exported CSV-style fields, tag lists, and "City, State" columns — anywhere one cell holds several values and the data refreshes often enough that manual Text to Columns becomes a chore.

Common mistakes

• ✕Pieces come back with stray leading spaces.

  ✓"Ana, Sales, Chicago" splits into " Sales" and " Chicago". In Excel wrap it: =TRIM(TEXTSPLIT(A2,",")). In Sheets, =ARRAYFORMULA(TRIM(SPLIT(A2,",",FALSE))) trims every piece.

• ✕TEXTSPLIT returns #NAME? in older Excel.

  ✓TEXTSPLIT is Excel 365 only. In Excel 2019 or earlier, use Data → Text to Columns, or extract pieces with TEXTBEFORE-style FIND and MID formulas.

• ✕#SPILL! appears instead of the split values.

  ✓Something is sitting in the cells the result needs to spill into. Clear the columns to the right of the formula.