Concatenate is Limiting Characters

[tcnt9176]

I have a large file with 133 columns that need to be combined into 1 column. Since there is a limit on the number of cells that can be combined using the concatenate formula, I actually split it up into 5 separate formulas and then 1 more to combine the 5. It seemed to work fine but when I looked at the finished product, it did not show complete data for each row. Since I was combining all of this into one cell, I thought it might be caused by the max characters allowed in a cell, but I looked up the max for 2007 and it said the max number of characters was 32,767, which mine are being cut off at approx. 1200 characters. Any ideas on what could be causing this. Here are my formulas. As you can see, each cell, when combined must have quotes and delimited with a comma. Thanks in advance!!!

=CONCATENATE(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&""""&","&"""")
=CONCATENATE(AA1&""""&","&""""&AB1&""""&","&""""&AC1&""""&","&""""&AD1&""""&","&""""&AE1&""""&","&""""&AF1&""""&","&""""&AG1&""""&","&""""&AH1&""""&","&""""&AI1&""""&","&""""&AJ1&""""&","&""""&AK1&""""&","&""""&AL1&""""&","&""""&AM1&""""&","&""""&AN1&""""&","&""""&AO1&""""&","&""""&AP1&""""&","&""""&AQ1&""""&","&""""&AR1&""""&","&""""&AS1&""""&","&""""&AT1&""""&","&""""&AU1&""""&","&""""&AV1&""""&","&""""&AW1&""""&","&""""&AX1&""""&","&""""&AY1&""""&","&""""&AZ1&""""&","&"""")
=CONCATENATE(BA1&""""&","&""""&BB1&""""&","&""""&BC1&""""&","&""""&BD1&""""&","&""""&BE1&""""&","&""""&BF1&""""&","&""""&BG1&""""&","&""""&BH1&""""&","&""""&BI1&""""&","&""""&BJ1&""""&","&""""&BK1&""""&","&""""&BL1&""""&","&""""&BM1&""""&","&""""&BN1&""""&","&""""&BO1&""""&","&""""&BP1&""""&","&""""&BQ1&""""&","&""""&BR1&""""&","&""""&BS1&""""&","&""""&BT1&""""&","&""""&BU1&""""&","&""""&BV1&""""&","&""""&BW1&""""&","&""""&BX1&""""&","&""""&BY1&""""&","&""""&BZ1&""""&","&"""")
=CONCATENATE(CA1&""""&","&""""&CB1&""""&","&""""&CC1&""""&","&""""&CD1&""""&","&""""&CE1&""""&","&""""&CF1&""""&","&""""&CG1&""""&","&""""&CH1&""""&","&""""&CI1&""""&","&""""&CJ1&""""&","&""""&CK1&""""&","&""""&CL1&""""&","&""""&CM1&""""&","&""""&CN1&""""&","&""""&CO1&""""&","&""""&CP1&""""&","&""""&CQ1&""""&","&""""&CR1&""""&","&""""&CS1&""""&","&""""&CT1&""""&","&""""&CU1&""""&","&""""&CV1&""""&","&""""&CW1&""""&","&""""&CX1&""""&","&""""&CY1&""""&","&""""&CZ1&""""&","&"""")
=CONCATENATE(DA1&""""&","&""""&DB1&""""&","&""""&DC1&""""&","&""""&DD1&""""&","&""""&DE1&""""&","&""""&DF1&""""&","&""""&DG1&""""&","&""""&DH1&""""&","&""""&DI1&""""&","&""""&DJ1&""""&","&""""&DK1&""""&","&""""&DL1&""""&","&""""&DM1&""""&","&""""&DN1&""""&","&""""&DO1&""""&","&""""&DP1&""""&","&""""&DQ1&""""&","&""""&DR1&""""&","&""""&DS1&""""&","&""""&DT1&""""&","&""""&DU1&""""&","&""""&DV1&""""&","&""""&DW1&""""&","&""""&DX1&""""&","&""""&DY1&""""&","&""""&DZ1&""""&","&""""&EA1&""""&","&""""&EB1&""""&","&""""&EC1&"""")
=CONCATENATE(“”””&ED1,EE1,EF1,EG1,EH1)

 

[nick401k]

Try this:

Use the concatenate formula where all you put is =CONCATENATE(A1,B1,C1,D1,E1) and so on. I have not tested this, but it works for small amounts of data. Maybe your formula is just too long?

 

[tcnt9176]

Nick. Thanks for your quick response. I actually took it and pasted it to a word document and it all showed up. I guess excel just stops showing it after a certain number of characters. Ultimately I am exporting any way so this will work for me. Thanks again!

 

[VoG]

I think that you've gone over the number of characters that can be displayed (not held) in a cell. See http://www.mrexcel.com/forum/showthread.php?t=77364 for example.