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

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Harlan Grove (hrlngrv_at_aol.com)
Date: 03/19/04 

Date: Fri, 19 Mar 2004 02:00:33 GMT

"firefytr <" wrote...
>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 

--
To top-post is human, to bottom-post and snip is sublime.
Quantcast