Copy all the value filled cells in a column into one cell

Asked Aug 07 '19 at 22:48

Active Aug 07 '19 at 22:51

Viewed 38 times

I would like to be able to copy and paste all of the value filled cells in column C of the worksheet into one cell with ", " dividing each of the copied cells.

This is what I have now. Works but the amount of filled cells varies.

=$C$5&", "&$C$6&", "&$C$7&", "&$C$8&", "&$C$9&", "&$C$10&", "&$C$11&", "&$C$12&", "&$C$13&", "&$C$14&", "&$C$15&", "&$C$16&", "&$C$17&", "&$C$18&", "&$C$19&", "&$C$20&", "&$C$21&", "&$C$22&", "&$C$23&", "&$C$24&", "&$C$25&", "&$C$26&", "&$C

edited Aug 07 '19 at 22:51

Scott Craner

148,073
10
49
81

asked Aug 07 '19 at 22:48

Tmacjoshua

3

Look at TEXTJOIN. If your version of Excel does not support it, there are questions here on SO about how to implement a UDF version of it. – BigBen Aug 07 '19 at 22:51
3

here is a udf that mimics TEXTJOIN: https://stackoverflow.com/questions/39532189/vlookup-with-multiple-criteria-returning-values-in-one-cell – Scott Craner Aug 07 '19 at 22:53
1

@ScottCraner thank you for providing that link, I was too lazy to look for it. – BigBen Aug 07 '19 at 22:54
1

@BigBen I have it starred so I can find it. – Scott Craner Aug 07 '19 at 22:55
@ScottCraner and now I do too lol. – BigBen Aug 07 '19 at 22:55
2

BTW, the formula would be `=TEXTJOIN(", ",TRUE,C5:C100)` – Scott Craner Aug 07 '19 at 22:56

Copy all the value filled cells in a column into one cell

0 Answers0