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

  • Thread starter Thread starter Guest
  • Start date Start date
G

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!
 
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
 
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
 
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
 
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
 
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
 
#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
 
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.
Click to expand...
...

?!

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
 
Ok I see what I was doing wrong, I was creating a function, not placin
in in just the general file.

Nice programing!!!

TyeJa
 
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
 
Back
Top