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))