Excel, how to put a named range list into one cell

Question

How do I put my named range of a list of values into one cell?

i've tried going into a cell and naming the range like so =namedrange

sample data in my named range:

+---+----------+
|   |    A     |
+---+----------+
| 1 | 'stack', |
| 2 | 'over',  |
| 3 | 'flow',  |
+---+----------+

=OFFSET(Nodes!$B$2,0,0,COUNTA(Nodes!$B:$B),1)

expected output, i would like a formula to do this:

score 0 · Answer 1 · answered Feb 05 '19 at 15:13

Lets do 2 cases. Say the Named Range is A1 through A3. Without Named Ranges we can use:

=TEXTJOIN(CHAR(10),TRUE,A1:A3)

With Named Ranges:

=TEXTJOIN(CHAR(10),TRUE,Stooges)

gives the same result.

To use the comma as a separator, replace the 10 with 44

1 Answers1