How do I split comma-separated text into separate rows in Excel?

Use this formula (drag it down for each row): =TRIM(MID(SUBSTITUTE($D$5,',',REPT(' ',999)),ROWS($29:29)*999-998,999)). Change the delimiter in SUBSTITUTE to match your separator.

What is the difference between Text-to-Columns and the dynamic text-to-rows formula?

Text-to-Columns produces static results - it splits once and the output does not update if the source text changes. The formula approach is dynamic and recalculates whenever the source cell changes.

How many items can the text-to-rows formula split from a single cell?

Theoretically, as many as you have rows of formula beneath the source cell. Drag the formula down to cover the maximum number of expected items from any source text.

Dynamic Text-to-Rows in Excel Using a Formula

One of the standard Excel features available is the Text-to-Columns feature which enables users to separate content within a cell into several columns based on a specified delimiter. What if, however,...

One of the standard Excel features available is the Text-to-Columns feature which enables users to separate content within a cell into several columns based on a specified delimiter.

What if, however, you wanted your data split into rows, not columns? You could split the text into columns using the Text-to-Column operation, and then select the results, copy them and transpose them to rows while pasting.

This works great. But what if you had a dynamic list, say, for an interactive dashboard, where the source text changed on selection, and you needed the text split to rows for each selection? The Text-to-Columns and transpose operation will not suffice as it generates static results. For this, you will need a formula.

The formula that performs this dynamic Text-to-Rows operations is a complex formula combining the =TRIM(), =MID(), =SUBSTITUTE(), =REPT() and =ROWS() functions. The syntax is below:

=TRIM(MID(SUBSTITUTE($D$5,",",REPT(" ",999)),ROWS($29:29)*999-998,999))

Split Delimited Text into Rows Dynamically in Excel (Formula Method)

Frequently Asked Questions