concatenate numbers, replacing blanks with comma across entire rows

[Dave01]

Hi,

Ive been handed a spreadsheet which Im trying to replicate in VBA, but Im trying to work out the best way to do it. I have a Row of Numbers , the rows span from column C -> Column FZ, 180 columns
From column 1 starting from C, I have various numbers, some have blanks inbetween, others not. and what I would like to do is concatenate the entire range. - See image

This was the formula that did it, but that has to be a much better way of doing it, especially in VBA

=CONCATENATE(IF(LEN(C3)=0,"",C3&", "),IF(LEN(D3)=0,"",D3&", "),IF(LEN(E3)=0,"",E3&", "),IF(LEN(F3)=0,"",F3&", "),IF(LEN(G3)=0,"",G3&", "),IF(LEN(H3)=0,"",H3&", "),IF(LEN(I3)=0,"",I3&", "),IF(LEN(J3)=0,"",J3&", "),IF(LEN(K3)=0,"",K3&", "),IF(LEN(L3)=0,"",L3&", "),IF(LEN(M3)=0,"",M3&", "),IF(LEN(N3)=0,"",N3&", "),IF(LEN(O3)=0,"",O3&", "),IF(LEN(P3)=0,"",P3&", "),IF(LEN(Q3)=0,"",Q3&", "),IF(LEN(R3)=0,"",R3&", "),IF(LEN(S3)=0,"",S3&", "),IF(LEN(T3)=0,"",T3&", "),IF(LEN(U3)=0,"",U3&", "),IF(LEN(V3)=0,"",V3&", "),IF(LEN(W3)=0,"",W3&", "),IF(LEN(X3)=0,"",X3&", "),IF(LEN(Y3)=0,"",Y3&", "),IF(LEN(Z3)=0,"",Z3&", "),IF(LEN(AA3)=0,"",AA3&", "),,IF(LEN(AB3)=0,"",AB3&", "),IF(LEN(AC3)=0,"",AC3&", "),IF(LEN(AD3)=0,"",AD3&", "),IF(LEN(AE3)=0,"",AE3&", "),IF(LEN(AF3)=0,"",AF3&", "),IF(LEN(AG3)=0,"",AG3&", "),IF(LEN(AH3)=0,"",AH3&", "),IF(LEN(AI3)=0,"",AI3&", "),IF(LEN(AJ3)=0,"",AJ3&", "),IF(LEN(AK3)=0,"",AK3&", "),IF(LEN(AL3)=0,"",AL3&", "),IF(LEN(AM3)=0,"",AM3&", "),IF(LEN(AN3)=0,"",AN3&", "),IF(LEN(AO3)=0,"",AO3&", "),IF(LEN(AP3)=0,"",AP3&", "),IF(LEN(AQ3)=0,"",AQ3&", "),IF(LEN(AR3)=0,"",AR3&", "),IF(LEN(AS3)=0,"",AS3&", "),IF(LEN(AT3)=0,"",AT3&", "),IF(LEN(AU3)=0,"",AU3&", "),IF(LEN(AV3)=0,"",AV3&", "),IF(LEN(AW3)=0,"",AW3&", "),IF(LEN(AX3)=0,"",AX3&", "),IF(LEN(AY3)=0,"",AY3&", "),IF(LEN(AZ3)=0,"",AZ3&", "),IF(LEN(BA3)=0,"",BA3&", "),IF(LEN(BB3)=0,"",BB3&", "),IF(LEN(BC3)=0,"",BC3&", "),IF(LEN(BD3)=0,"",BD3&", "),IF(LEN(BE3)=0,"",BE3&", "),IF(LEN(BF3)=0,"",BF3&", "),IF(LEN(BG3)=0,"",BG3&", "),IF(LEN(BH3)=0,"",BH3&", "),IF(LEN(BI3)=0,"",BI3&", "),,IF(LEN(BJ3)=0,"",BJ3&", "),IF(LEN(BK3)=0,"",BK3&", "),IF(LEN(BL3)=0,"",BL3&", "),IF(LEN(BM3)=0,"",BM3&", "),IF(LEN(BN3)=0,"",BN3&", "),IF(LEN(BO3)=0,"",BO3&", "),IF(LEN(BP3)=0,"",BP3&", "),IF(LEN(BQ3)=0,"",BQ3&", "),IF(LEN(BR3)=0,"",BR3&", "),IF(LEN(BS3)=0,"",BS3&", "),IF(LEN(BT3)=0,"",BT3&", "),IF(LEN(BU3)=0,"",BU3&", "),IF(LEN(BV3)=0,"",BV3&", "),IF(LEN(BW3)=0,"",BW3&", "),IF(LEN(BX3)=0,"",BX3&", "),IF(LEN(BY3)=0,"",BY3&", "),IF(LEN(BZ3)=0,"",BZ3&", "),IF(LEN(CA3)=0,"",CA3&", "),IF(LEN(CB3)=0,"",CB3&", "),IF(LEN(CC3)=0,"",CC3&", "),IF(LEN(CD3)=0,"",CD3&", "),IF(LEN(CE3)=0,"",CE3&", "),IF(LEN(CF3)=0,"",CF3&", "),IF(LEN(CG3)=0,"",CG3&", "),IF(LEN(CH3)=0,"",CH3&", "),IF(LEN(CI3)=0,"",CI3&", "),IF(LEN(CJ3)=0,"",CJ3&", "),IF(LEN(CK3)=0,"",CK3&", "),IF(LEN(CL3)=0,"",CL3&", "),IF(LEN(CM3)=0,"",CM3&", "),IF(LEN(CN3)=0,"",CN3&", "),IF(LEN(CO3)=0,"",CO3&", "),IF(LEN(CP3)=0,"",CP3&", "),IF(LEN(CQ3)=0,"",CQ3&", "),IF(LEN(CR3)=0,"",CR3&", "),IF(LEN(CS3)=0,"",CS3&", "),IF(LEN(CT3)=0,"",CT3&", "),IF(LEN(CU3)=0,"",CU3&", "),IF(LEN(CV3)=0,"",CV3&", "),IF(LEN(CW3)=0,"",CW3&", "),IF(LEN(CX3)=0,"",CX3&", "))


Is there any way of cutting this Wall of Formula down and making it work in VBA for speed. (this is only on one cell, it then has to go down to the bottom of the row based on how many rows there are on A.

thanks
David

 

[jtakw]

the rows go all the way up to column GA, so its allot of formula per cell.

I know, but it'll be shorter than the one you're currently using.

 

[Dave01]

with the function, oh ok, I see

 

[Dave01]

with the function, oh ok, I see

with the code and tthe formula =xCONCAT(C3:L3), I go from the error Namne to 0, textjoin I cant use, seems it was for a later version.

Ill give this a go, and try to convert it to VB, theres some 500 lines it will need to be applied to

SUBSTITUTE(TRIM(C3&" "&D3&" "&E3&" "&F3&" "&G3&" "&H3)," ",", ")

 

[Dave01]

=xCONCAT(C3:L3) yes this worked, I included the formula cell as part of the calculation cell.

thanks guys for your help.