Split a Delimited List into Rows

Excel 365Legacy alt

To turn a comma-separated value in one cell into a vertical list — tags, emails, line items pasted into a single cell — use TEXTSPLIT with a row delimiter. The pieces spill down a column instead of across.

Quick formula: to split the text in A2 onto separate rows at each comma:

=TEXTSPLIT(A2, , ",")

The 2nd argument is the column delimiter (left blank here); the 3rd is the row delimiter — so pieces stack vertically.

Functions used (tap for the full reference guide):

TEXTSPLIT TEXTAFTER

The example

A comma list in one cell, split down a column.

	A	C
1	List (one cell)	Rows
2	red,blue,green	red
3		blue
4		green

The formula

The list split into a column:

=TEXTSPLIT(A2, , ",") // "red,blue,green" → red / blue / green (down)

How it works

The third argument is what makes it go down, not across:

TEXTSPLIT’s arguments are (text, col_delimiter, row_delimiter, …).
Leaving the column delimiter blank and giving a row delimiter (",") tells it to break onto new rows at each comma.
The pieces spill down a single column — ready to FILTER, SORT, or look up.
Add a 4th argument TRUE to ignore empty pieces from doubled-up delimiters.

Legacy / no 365: use Data → Text to Columns then Transpose, or Power Query’s “Split column → By delimiter → Into rows.” TEXTSPLIT is by far the quickest when you have it.

Try it: interactive demo

Live demo

Type a delimited list and the delimiter; see it split into rows.

Text (A2)

Delimiter

Variations

Split to columns instead

Use the column delimiter (2nd argument):

=TEXTSPLIT(A2, ",")

Split on multiple delimiters

Pass an array of separators:

=TEXTSPLIT(A2, , {",";";"})

Split, then clean each piece

Trim spaces around items:

=TRIM(TEXTSPLIT(A2, , ","))

Pitfalls & errors

Mind the empty 2nd argument. To split to rows, the column delimiter must be left blank and the row delimiter supplied: TEXTSPLIT(A2, , ",").

TEXTSPLIT needs Excel 365. Excel 2021 and older show #NAME? — use Text to Columns + Transpose, or Power Query.

#SPILL! Clear the cells below so the rows can spill.

Practice workbook

📊

Download the free Split a Delimited List into Rows practice workbook

TEXTSPLIT-to-rows examples (single and multi-delimiter, with trim) shown with results, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I split a comma-separated cell into rows in Excel?

Use =TEXTSPLIT(A2, , ",") with the column delimiter blank and the comma as the row delimiter, so the pieces spill down a column. Requires Excel 365.

How do I split to rows in older Excel?

Use Data > Text to Columns, then Copy and Paste Special > Transpose, or use Power Query's Split Column > By Delimiter > Into Rows.

How do I split on more than one delimiter?

Pass an array as the delimiter: =TEXTSPLIT(A2, , {",";";"}) splits on both commas and semicolons.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Function references: TEXTSPLIT · TEXTAFTER