# How to split text into columns with a formula in Excel

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

**Excel:** `=TEXTSPLIT(A2,", ")` (Excel 2024 and Microsoft 365)

**Google Sheets:** `=SPLIT(A2,", ",FALSE)`

## Example

Sample data (row 1 is the header):

| A | B | C | D |
| --- | --- | --- | --- |
| Address | Street | City | State |
| 1420 Lake St, Minneapolis, MN |  |  |  |
| 88 Oak Ave, Saint Paul, MN |  |  |  |
| 7 Elm Ct, Madison, WI |  |  |  |
| 305 River Rd, Fargo, ND |  |  |  |
| 12 Pine Ln, Duluth, MN |  |  |  |

Formula in B2, filled to B6: `=TEXTSPLIT(A2,", ")`

Result: 1420 Lake St; 88 Oak Ave; 7 Elm Ct; 305 River Rd; 12 Pine Ln

## 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.

## Other ways

- `=TRIM(MID(SUBSTITUTE($A2,",",REPT(" ",100)),(COLUMNS($B2:B2)-1)*100+1,100))`: 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.

## 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,{",",";"})`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/split-text-into-columns-with-a-formula