Merge values of an array into a single cell

Question

I have an array that I would like to put into a single cell, with commas to make a list. I'm starting with an array of varying length in column BY:

Red Blue Green Yellow Purple Brown White Black Orange

and I want to have all of these put into a cell so it reads, "Red, Blue, Green, Yellow, Purple, Brown, White, Black, Orange"

The list is dynamic, so sometimes it will be just 1 color and other times it may have 50 colors.

I was trying things like,

=concatenate(arrayformula('1'!BY1:BY))

but that returns RedBlueGreenYellowPurpleBrownWhiteBlackOrange

Any ideas for me? Thank you in advance! NJD

Neill · Accepted Answer · 2017-09-30 04:21:48Z

2

The function you want is join, as in

=join(", ", BY1:BY)

or, since you probably want to exclude empty cells at the bottom of that column,

=join(", ", filter(BY1:BY, len(BY1:BY)))

where filter keeps only nonempty cells.

Neill

4522 gold badges6 silver badges20 bronze badges

answered Sep 30, 2017 at 2:58

user6655984

Sign up to request clarification or add additional context in comments.

1 Answer 1