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:

Go on Stack overflow and look at the answers for the TEXTJOIN tag. It is in there multiple times. — Scott Craner, Feb 05 '19 at 15:37

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

0

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

answered Feb 05 '19 at 15:13

Gary's Student

what is `textjoin` i dont seem to have that formula. I guess i dont have that excel version. – excelguy Feb 05 '19 at 15:16
may be worth noting TEXTJOIN is not available in 2013 or earlier. not sure when it was brought in. I think @ScottCraner has VBA code to produce TEXTJOIN results. – Forward Ed Feb 05 '19 at 15:17

1 Answers1