Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to split text down a whole column with ARRAYFORMULA in Excel and Google Sheets
Topic:ARRAYFORMULA (Google Sheets)Heads up: Excel and Google Sheets do this differently.
Excel
=TEXTSPLIT(A2,",")Google Sheets
=ARRAYFORMULA(SPLIT(A2:A4,","))Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1In Google Sheets, place your delimited data in column A (e.g., 'Alice,30,Engineer').
- 2In an empty cell on the same row as your first data row, type =ARRAYFORMULA(SPLIT(A2:A100,",")) and press Enter.
- 3Sheets splits each cell into separate columns for every row automatically — ensure the columns to the right are empty.
- 4In Excel 365, use =TEXTSPLIT(A2,",") per row, or use Data → Text to Columns for a one-time static split.
Tips
- ARRAYFORMULA(SPLIT(...)) can't spill into a range that already has data — clear the destination columns first, or it will throw a #REF! error.
Need the Google Sheets version instead? Open Sheets variant in workspace →
Working on a sheet you inherited? Run the Auditor on the whole file first — it flags every #REF!, #N/A, broken column pattern, and inconsistent formula in seconds, free, no signup.
Frequently asked
Can I use ARRAYFORMULA with SPLIT for multiple delimiters?
Yes — SPLIT accepts a string of delimiter characters or you can use a regex pattern: =ARRAYFORMULA(SPLIT(A2:A100,",|;")).
Does Excel have SPLIT like Google Sheets?
Excel 365 has TEXTSPLIT which works similarly. For older Excel, use Data → Text to Columns or the LEFT/MID/FIND combination.
More on ARRAYFORMULA (Google Sheets)
See all →Reference
ARRAYFORMULA (Google Sheets)
Apply a formula down an entire column at once in Google Sheets.
How-toHow to use ARRAYFORMULA to apply a formula to a whole column
Wrap any formula in ARRAYFORMULA in Google Sheets to apply it to an entire column without copying it to each row.
Error fixFix ARRAYFORMULA
ARRAYFORMULA not working in Google Sheets? Learn why it stops expanding, returns one row, or shows an error — and how to fix it.