Converting a Single Column to a Row with Commas?

Hi All,

I'm new here and am looking for some basic help. I've been searching and I'm sure this is much easier than I'm finding.

I have a long column with a list of #'s. What formula would I use to make it a single row separated by commas?

Example:

Column A1:
1
2
3
4
5

I want it to read 1,2,3,4,5

I know there is a formula that looks something like this:
=A1&","&A2&","&A3&","&A4&","... etc

or
=concatenate(A1,",",A2,",",A3,",",A4,","... etc

My question is, if I have 50 numbers in a column, how could I generate this without having to type out the &"," for each one? I tried dragging it down, but that didn't work. I know nothing about VBA and this will be with random spreadsheets, so I'm not looking to do anything with macros. I'm trying to create a tutorial for my staff for this formula when they need to use it (so that they can apply it to any situation and just replace the column info if needed).

Whew! I hope I made sense. I'm really looking for a 101, basic formula.
Thanks SOOOOO much in advance!

Originally Posted by Richard Buttrey

Hi,

In B1
=A1
In B2 and copied down
=B1&","&A2

Then copy the last cell in column B and paste it back as a value.

Regards

Hi Richard,

Thank you for your reply. I'm going to sound like an idiot but what do you mean by copy the last cell in column B and paste it back as a value? Also- once I do that, how do I I make it apply to all of the cells that I want to include? Again, thank you so much.

Originally Posted by abousetta

Hello and welcome to the forum,

There is probably a really good formula to do this in one cell but I use a helper column. It goes like this:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Also I could whip up a user-defined function if you want.

Let me know.

Good luck.

abousetta

abousetta,
Thank you for your reply! I'm trying to understand a helper formula, how do you apply what you pasted above? I would greatly appreciate a user-defined function. Thank you so again! I never thought that a simple thing could turn out so complicated! LOL :D

Originally Posted by Richard Buttrey

Hi,

I assumed that you wanted a single cell that contained the concatenation of all the numbers. So after copying the formula in B2 down to the last cell, say B50, copy the cell and then from the menu select Paste Special Values. This will convert the formula in B50 to a text value.

If you want to convert all the cells in column B just copy B1:B50 and again use Paste Special Values to paste back to B1.

Richard,
Thank you!! Yes, I want the single cell to go horizontal instead of vertical.

1. Column B1 I typed: =A1

2. Column B2 I typed: =B1&","&A2

3. I dragged (copied) down to cell B9. Column B now looks like this:
123
123,45
123,45,234
123,45,234,789
123,45,234,789,102
123,45,234,789,102,145
123,45,234,789,102,145,78965
123,45,234,789,102,145,78965,12365
123,45,234,789,102,145,78965,12365,12345

4. Copied B9 and pasted as "Special Values" back to B1. Column B1 now looks like this:
123,45,234,789,102,145,78965,12365,12345,45,234,789,102,145,78965,12365,12345

My last question would be this: When I pasted back, it lost the formula, so if I added additional cells, it won't auto calculate. Would I just run the formula again? Is there a way to make this a default general function or action (not sure what to call it) so that I could apply it to any spreadsheet that I'm working in?

abousetta,

Thank you so much! How would I apply this to my spreadsheets whenever I want to use it? I'm sorry for all of the questions, but this is new to me.

Originally Posted by Richard Buttrey

Well if the other sheets always have the same cell relationships you could just keep the B1 & B2 formula somewhere and then just copy and paste them to the ranges you're interested in.

You lost the formula because of the Paste Special Values action. If you don't want to lose them just copy B9 and Paste Values to a cell other than B9 in this example, say C9.

Duh, that makes sense too. Thanks again so much for humoring my basic questions. Is there a particular forum folder or website that I can go to to learn the basics of macros/VBS?

Great. Thank you!! So if I'm in the workbook, I click Ctr & F11, select "View/Macros/Record Macros"?

Thank you so much for your patience in explaining that to me. I have a lot of learning to do!

Thanks a lot for your explanation!