How to combine multiple cells into 1 cell with comma between each number

[Guest]

Is it possible to take a range of cells (in a column) and combine the data into 1 cell, with commas in between each number? For example, I have a spreadsheet with a list of zip codes, each one in a seperate cell in the same column. I would like to combine all of the zip codes into 1 cell, with a comma (and space if possible) in between each zip code. I would ultimately like to be able to take the combined data and copy and paste it into a word document. Some of the lists of zip codes contain 100-200 zip codes, so I would like to be able to not have to retype them all to combine them if possible

Any help would be appreciated!

 

[firefytr]

take a look at the CONCATENATE function.

or use the "&" symbol - same as concatenate.

your still limited to 32 different cell references doing that though.
maybe link 3 or 4 together? i dunno.

maybe you oughta think about a vba way through it, if it's too many.

HTH

 

[Gord Dibben]

sbjen

It is possible, but a lot of typing to do it without a User Defined Function
or a macro.

UDF......

Function ConCatRange(CellBlock As Range) As String
Dim cell As Range
Dim sBuf As String
For Each cell In CellBlock
If Len(cell.text) > 0 Then sBuf = sBuf & cell.text & ", "
Next
ConCatRange = Left(sBuf, Len(sBuf) - 1)
End Function

Copy/paste this code to a general module in your workbook then in a cell enter

=ConCatRange(A1:A100)

Gord Dibben Excel MVP

 

[TyeJae]

Not sure if this is the easiest way, but it works...

If your Data Range is C2:C31 then your formula would be this:

=C2&", "&C3&", "&C4&", "&C5&", "&C6&", "&C7&", "&C8&", "&C9&", "&C10&"
"&C11&", "&C12&", "&C13&", "&C14&", "&C15&", "&C16&", "&C17&", "&C18&"
"&C19&", "&C20&", "&C21&", "&C22&", "&C23&", "&C24&", "&C25&", "&C26&"
"&C27&", "&C28&", "&C29&", "&C30&", "&C31

You would have to keep going with this formula to get up to 100's.

TyeJa

 

[firefytr]

TyeJae,

this works, but only up to 32 unique entries. so if you're looking at
32+, Gord's UDF is the way to go.

HTH

 

[TyeJae]

I coulnd't get the UDF to work, but yes you are limited to 32, but yo
could have mutiple cells with the formulas then add those cell
together, still alot of work though lo

 

[TyeJae]

I get #NAME? when I try to use the function. Am I doing somethin
wrong

 

[Gord Dibben]

#NAME means Excel cannot find the Function.

A UDF is entered into a general module in your workbook then called from a
cell.

With your workbook open..........

Hit ALT + F11 to open Visual Basic Editor.

Find your workbook/project in the Project Explorer and
right-click>Insert>Module

Paste the ConCatRange code in there.

ALT + Q to go back to your worksheet window.

In an empty cell enter =ConCatRange(A1:A100)

Gord

 

[Harlan Grove]

take a look at the CONCATENATE function.

or use the "&" symbol - same as concatenate.

your still limited to 32 different cell references doing that though.
maybe link 3 or 4 together? i dunno.

...

?!

You're limited only by formula length. With the following array formula in
A1:F6, ={"A","B","C","D","E","F"}&{1;2;3;4;5;6}, the following formula in A8,

=A1&", "&A2&", "&A3&", "&A4&", "&A5&", "&A6&", "&B1&", "&B2&", "&B3&", "&
B4&", "&B5&", "&B6&", "&C1&", "&C2&", "&C3&", "&C4&", "&C5&", "&C6&", "&
D1&", "&D2&", "&D3&", "&D4&", "&D5&", "&D6&", "&E1&", "&E2&", "&E3&", "&
E4&", "&E5&", "&E6&", "&F1&", "&F2&", "&F3&", "&F4&", "&F5&", "&F6

gives

A1, A2, A3, A4, A5, A6, B1, B2, B3, B4, B5, B6, C1, C2, C3, C4, C5, C6,
D1, D2, D3, D4, D5, D6, E1, E2, E3, E4, E5, E6, F1, F2, F3, F4, F5, F6

and the formula

=CONCATENATE(CONCATENATE(A1,", ",A2,", ",A3,", ",A4,", ",A5,", ",A6,", "),
CONCATENATE(B1,", ",B2,", ",B3,", ",B4,", ",B5,", ",B6,", "),
CONCATENATE(C1,", ",C2,", ",C3,", ",C4,", ",C5,", ",C6,", "),
CONCATENATE(D1,", ",D2,", ",D3,", ",D4,", ",D5,", ",D6,", "),
CONCATENATE(E1,", ",E2,", ",E3,", ",E4,", ",E5,", ",E6,", "),
CONCATENATE(F1,", ",F2,", ",F3,", ",F4,", ",F5,", ",F6))

also gives

A1, A2, A3, A4, A5, A6, B1, B2, B3, B4, B5, B6, C1, C2, C3, C4, C5, C6,
D1, D2, D3, D4, D5, D6, E1, E2, E3, E4, E5, E6, F1, F2, F3, F4, F5, F6

 

[TyeJae]

Ok I see what I was doing wrong, I was creating a function, not placin
in in just the general file.

Nice programing!!!

TyeJa

 

[firefytr]

Harlan,

that's a very interesting way of doing that. i myself, have just bee
dabbling in array uses and functions. but that's pretty slick. thank
btw