Reputation: 83

Excel text to rows using formula

I have a concat formula returning me a comma separated list of names in a cell

=concat(A2:A10 & " ,") returning [john, jack, jill] in the cell

Is there a way to add to this formula to expand to

john
jack
jill

in a column like that above?

Upvotes: 0

Answers (2)

Reputation: 83

I used Power Editor to convert the cell into rows

Upvotes: 0

Reputation: 5542

You can change the separator to be a line break =CONCAT(A2:A10 & CHAR(10)) or =TEXTJOIN(CHAR(10),TRUE,P3:P5)

The target cell needs to be set to Wrap Text for the line breaks to be displayed correctly.

Upvotes: 0