XL97

How can I concatenate multiple adjacent cells?

In Help it states that the Concatenate function works as
*=Concatenate(text1,text2...)* but only up to a max of 30.

Is there any way to do, for example, the equivalent of
*=Concatenate(A1:AZ1)* ?


TIA

Harry

See

http://www.mcgimpsey.com/excel/udfs/multicat.html


In article ,
"Harry" wrote:

XL97

How can I concatenate multiple adjacent cells?

In Help it states that the Concatenate function works as
*=Concatenate(text1,text2...)* but only up to a max of 30.

Is there any way to do, for example, the equivalent of
*=Concatenate(A1:AZ1)* ?


TIA

Harry

Harry wrote...
XL97

How can I concatenate multiple adjacent cells?

In Help it states that the Concatenate function works as
*=Concatenate(text1,text2...)* but only up to a max of 30.

Is there any way to do, for example, the equivalent of
*=Concatenate(A1:AZ1)* ?

Another way: don't use CONCATENATE. Use the & operator instead.

=A1&B1&C1&D1&E1&F1&G1&H1&I1&J1&K1&L1&M1&N1&O1&P1
&Q1&R1&S1&T1&U1&V1&W1&X1&Y1&Z1&AA1&AB1&AC1&AD1
&AE1&AF1&AG1&AH1&AI1&AJ1&AK1&AL1&AM1&AN1&AO1&AP 1
&AQ1&AR1&AS1&AT1&AU1&AV1&AW1&AX1&AY1&AZ1

And if this seems like too much typing, create this formula following
these steps.

1. Type =ADDRESS(1,COLUMN(A:AZ)), press in sequence [F9] [F2] [End]
[Backspace] [Ctrl]+[Home] [Delete] [Delete] [Enter]. This should leave
the text string

"$A$1","$B$1","$C$1","$D$1","$E$1","$F$1","$G$1"," $H$1","$I$1","$J$1",
"$K$1","$L$1","$M$1","$N$1","$O$1","$P$1","$Q$1"," $R$1","$S$1","$T$1",
"$U$1","$V$1","$W$1","$X$1","$Y$1","$Z$1","$AA$1", "$AB$1","$AC$1",
"$AD$1","$AE$1","$AF$1","$AG$1","$AH$1","$AI$1","$ AJ$1","$AK$1",
"$AL$1","$AM$1","$AN$1","$AO$1","$AP$1","$AQ$1","$ AR$1","$AS$1",
"$AT$1","$AU$1","$AV$1","$AW$1","$AX$1","$AY$1","$ AZ$1"

2. Select this cell and a blank cell and run the menu command Edit
Replace. First replace " with nothing. Then replace $ with nothing.
Finally, replace , with & . This should leave the text string

A1&B1&C1&D1&E1&F1&G1&H1&I1&J1&K1&L1&M1&N1&O1&P1
&Q1&R1&S1&T1&U1&V1&W1&X1&Y1&Z1&AA1&AB1&AC1&AD1
&AE1&AF1&AG1&AH1&AI1&AJ1&AK1&AL1&AM1&AN1&AO1&AP 1
&AQ1&AR1&AS1&AT1&AU1&AV1&AW1&AX1&AY1&AZ1

3. Press in sequence [F2] [Ctrl]+[Home] = [Enter].

This should convert it to the formula above.