CONCATENATE formula, Comma seperate

[dmcg9760]

Merry Christmas All


I have attached a excel table that I am having issues getting a comma separation of numbers when using a CONCATENATE formula Column DS and DT.

I am not sure whether I need to use vba to get this result as am still learning in excel.
Much appreciated if any one can help with this.

[Debaser]

Change the formulas in columns DM to DP to this:
DM5:
=SUBSTITUTE(TRIM(CONCATENATE(BS5&" "&BW5&" "&CA5&" "&CE5&" "&CI5&" "&CM5&" "&CQ5&" "&CU5))," ",",")
DN5:
=SUBSTITUTE(TRIM(CONCATENATE(BT5&" "&BX5&" "&CB5&" "&CF5&" "&CJ5&" "&CN5&" "&CR5&" "&CV5))," ",",")
DO5:
=SUBSTITUTE(TRIM(CONCATENATE(BU5&" "&BY5&" "&CC5&" "&CG5&" "&CK5&" "&CO5&" "&CS5&" "&CW5))," ",",")
DP5:
=SUBSTITUTE(TRIM(CONCATENATE(BV5&" "&BZ5&" "&CD5&" "&CH5&" "&CL5&" "&CP5&" "&CT5&" "&CX5))," ",",")

and similarly with the formulas in columns BN:BQ, then your formulas in DS and DT will work as they are.

TRIM removes any leading and trailing spaces, and replaces any consecutive spaces within the text with a single space. SUBSTITUTE then replaces those single spaces with commas.

[dmcg9760]

Hi Debaser
Cheers for that mate
Works a treat
Very nice work
Sheet is a bit messy but as long as it outputs the result is the main thing. as I am a beginner in excel the other formulas are a bit long and drawn out
I have been struggling with that formula all day
Thanks very Much
Merry Christmas

[dmcg9760]

Hi Debaser
Just a quick one on that attached Excel table.
is there a quick way to make all the formulas from AH4U500 absolute references other than clicking on each individual formula and clicking F4 Button

[Pecoflyer]

Try Ctrl+H
replace AH4:U500
with $AH$4:$U$500
look in : Formula
Replace All
( I can't read the real range, smiley)