0

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?

maxokream
  • 43
  • 5
  • If you have Office365 (or Excel 2021) you can simply write `=A2:a10` in a single cell and the results will spill down. Earlier versions of Excel, without dynamic arrays, will require a formula in each cell where you want to show a result. – Ron Rosenfeld Nov 05 '21 at 01:19

2 Answers2

0

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.

Joao Leal
  • 5,533
  • 1
  • 13
  • 23
  • Thanks, didnt know CHAR(10) was how to add a newline, thats helpful. However ideally i'd be able to get the values in separate cells. – maxokream Nov 05 '21 at 00:42
  • I thought you wanted them in the same cell (can you put a screenshot with an example of how you want it to work?) – Joao Leal Nov 05 '21 at 00:55
0

I used Power Editor to convert the cell into rows

maxokream
  • 43
  • 5