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

 

[Marc L]

Hi,​

​

what is the final purpose ?​

 

[lrobbo314]

Book2
	A	B	C	D	E	F	G	H	I	J	K	L
3	VBA	10, 20, 30, 40, 50, 60	10	20		30		40		50		60
4	O365	10, 20, 30, 40, 50, 60
Sheet6

Cell Formulas
Range	Formula
B3	B3	=xCONCAT(C3:L3)
B4	B4	=TEXTJOIN(", ",1,C3:L3)

Function xCONCAT(r As Range)
Dim AR() As Variant: AR = r.Value2
For i = 1 To UBound(AR, 2)
    If AR(1, i) <> vbNullString Then xCONCAT = xCONCAT & AR(1, i) & ", "
Next i
xCONCAT = Left(xCONCAT, Len(xCONCAT) - 2)
End Function

 

[Dave01]

Its to get what you see in column B, the final purpose appears to be how many entries appear on a PO line. for a document number.

 

[Marc L]

​

As it could be done without looping each cell of the row and could be without an UDF but try first the speed of post #3 UDF​

or just using TEXTJOIN if your Excel version has it …​

 

[jtakw]

Hi,

Don't know about VBA, and if you don't have TEXTJOIN, you can shorten your CONCATENATE formula like this:

Book3.xlsx
	B	C	D	E	F	G	H
3	40, 50, 90	40	50				90
Sheet927

Cell Formulas
Range	Formula
B3	B3	=SUBSTITUTE(TRIM(C3&" "&D3&" "&E3&" "&F3&" "&G3&" "&H3)," ",", ")

 

[Dave01]

Hi,

Thanks for comming back

i dont have the commands in my excel

=xCONCAT & TEXTJOIN

I tried that earlier but it came up with #Name

 

[Dave01]

Hi,

Don't know about VBA, and if you don't have TEXTJOIN, you can shorten your CONCATENATE formula like this:

Book3.xlsx
	B	C	D	E	F	G	H
3	40, 50, 90	40	50				90
Sheet927

Cell Formulas
Range	Formula
B3	B3	=SUBSTITUTE(TRIM(C3&" "&D3&" "&E3&" "&F3&" "&G3&" "&H3)," ",", ")

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

 

[jtakw]

xCONCAT is Not a native function, it's a UDF Irobbo314 wrote, you need to install the code.

 

[Dave01]

I have excel 2016