Convert Column Into Comma-Separated List: Getting Around Character Limit

Hey, guys!

I'm struggling with a little challenge. I have a list of entries in Column A, in each cell there are 2-3 words, around 17 000 rows in whole column, and 160 000 characters in whole column. I need to take each cell in Column A and put them into a comma-separated list.

So if Column A looks like this:
Cow
Apple
Cider
Banana

I need a macro that would make it into: Cow, Apple, Cider, Banana

I found the following macro online:

Please Login or Register  to view this content.

It stores resulting list in a cell (B1). The problem is that since I have more than 160 000 characters in the column, it exceeds Excel's cell character limit of 32 767.
Is there way to store result in clipboard maybe or some other workaround?

What do you plan to use it for?

Originally Posted by rorya

What do you plan to use it for?

It goes as an input (I'm pasting it there) for a web service.

It used to work with a vertical column list, so I could just select whole Column A and copy/paste it. But now this service requires a comma-separated list, so I need a macro that would convert column into a comma-separated list. Storing in clipboard sounds like a viable option, since I'm pasting the resulting comma list somewhere else anyhow.

You could use a function like this to put the text in the clipboard:

Please Login or Register  to view this content.

Originally Posted by rorya

You could use a function like this to put the text in the clipboard:

Please Login or Register  to view this content.

Thank you for the input, rorya! Since I'm not very strong in VBA I don't know how to properly apply this to a the macro I quoted. Any chance you could be so kind to show how does it actually apply?

Add that as a separate function, then replace this:

Please Login or Register  to view this content.

in your code with this:

Please Login or Register  to view this content.

Originally Posted by rorya

Add that as a separate function, then replace this:

Please Login or Register  to view this content.

in your code with this:

Please Login or Register  to view this content.

Thank you, rorya, much obliged!


Purely for posterity. This is what I added into a Module in my file:

Please Login or Register  to view this content.

So far so good! :D