How to concatenate text cells but skip blank cells

Hello,

On my sample spreadsheet, I would like to concatenate A1:A15 into a single cell (into say cell B1), separating each cell by a comma and space, and skip all blank cells. These cells will actually be formulas, so that’s why I will sometimes have blank cells that can’t be removed beforehand. Also, no long nested IF’s, because I will expanding the column to over 50 rows. The final solution should generate:

Lions, Blue Whales, Bottle-nose Dolphins, Tiger, Sloth, Gremlin, Red Wolf, Bald Eagle

So, I also need to skip the last comma and space as well. I’ve tried using Substitute, but it wants to put commas between every single word. I would prefer a non-VBA solution. Thanks!!!

Thanks Alkey especially. That was so easy and works perfectly!!! So much for a macro-free workbook, this is just too slick NOT to use. Thanks to newdoverman too, but since I sometimes have as many as 50 rows, that's just too cumbersome.

Is there a way to modify the macro below that Alkey supplied to also alphabetize the list? I'd rather do that than making additional columns that are already alphabetized. Thanks!!!

Please Login or Register  to view this content.