CONCATENATE an entire column

[db9429]

Hi everyone!

I'm trying to concatenate an entire column with a certain character between each cell.

I've found a few options related to VB script, but I'm not too good with that so I was wondering if there was an alternative?

Thanks in advance!

[oeldere]

Maybe something like this?

c1

Please Login or Register  to view this content.

And drag down.

[Moo the Dog]

Sure, providing your list isn't too long, you could use a formula. If your list starts in A1, then in B1, enter:
=A1
Then, in B2, enter: =B1&", "&A2
Then fill that down. To change the separator, replace the comma with whatever you want.

Once you get to the bottom of the list in column A, it will have concatenated the column, with separators.
- Moo

[Kevin UK]

Hi db9429

What do you mean by concatenating a entire column! If you wanted to concatenate A1 to B1 with Characters in between.
=A1&CHAR(65)&CHAR(66)&B1
And copy down!

[db9429]

Hi everyone, thanks for your replies.

What I actually mean (I don't think I did a good job of explaining it) is I've got a list from B1 to B100, each with a different value.

So instead of it looking like this:
B1Contents
B2Contents
B3Contents
B4Contents
etc
B100Contents

I need it to concatenate each value so it looks like this: B1Contents,B2Contents,B3Contents,B4Contents,B5Contents etc

So it would be quite a lot in one cell!

Maybe I do need to use VBScript?

[db9429]

Just to clarify, the technique that has been suggested concatenates only 2 cells. So my list is in B1 to B100

in C2 I've done =B1&", "&B2 and dragged down and I'm getting:

B1Contents, B2Contents
B2Contents, B3Contents
B3Contents, B4Contents

etc

Thanks

[db9429]

Just to clarify, the technique that has been suggested concatenates only 2 cells. So my list is in B1 to B100

in C2 I've done =B1&", "&B2 and dragged down and I'm getting:

B1Contents, B2Contents
B2Contents, B3Contents
B3Contents, B4Contents

etc

Thanks

[dredwolf]

actually, you should be using , In C1: =B1, in C2 :=C1&" , "&B2 dragged down..

Hope this helps

[db9429]

dredwolf - that was it! all working now.

Thank you all so much!

[dredwolf]

You are welcome

[dredwolf]

Moo's formula was right, you just forgot to adjust for moving it over a column

[Marcol]

This UDF might be of some interest
It concatinates any given range with the sepatator of your choice, omitting blank cells.

See H22