concatenate numbers, replacing blanks with comma across entire rows

Dave01

Board Regular
Joined
Nov 30, 2018
Messages
116
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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
 

Attachments

  • Capture.JPG
    Capture.JPG
    76.4 KB · Views: 23

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
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)," ",", ")
 
Upvote 0
=xCONCAT(C3:L3) yes this worked, I included the formula cell as part of the calculation cell.

thanks guys for your help.
 
Upvote 0

Forum statistics

Threads
1,224,820
Messages
6,181,160
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top