Concatenate without zero nominal

[wiewie002]

Dear Guys,
I need to take series number without 0 (zero) from a series of number.
please help to advise the formula in column A3.
Attached file for detail. Thank you in advance.

Without zero.xlsx

Cheers,
Wie

[berlan]

One way would for instance be (based on inputs in A2:I2),

=SUBSTITUTE(TRIM(IF(RIGHT(A2,2)<>"/0",A2,"")&" "&IF(RIGHT(B2,2)<>"/0",B2,"")&" "&IF(RIGHT(C2,2)<>"/0",C2,"")&" "&IF(RIGHT(D2,2)<>"/0",D2,"")&" "&IF(RIGHT(E2,2)<>"/0",E2,"")&" "&IF(RIGHT(F2,2)<>"/0",F2,"")&" "&IF(RIGHT(G2,2)<>"/0",G2,"")&" "&IF(RIGHT(H2,2)<>"/0",H2,"")&" "&IF(RIGHT(I2,2)<>"/0",I2,""))," ",", ")

Another way would be using VBA...

[Glenn Kennedy]

Here's a VBA solution, using a UDF that I "borrowed" from Chip Pearson. Open with macros enabled. the syntax is:

=StringConcat(", ",IF(RIGHT($A$2:$I$2,2)<>"/0",$A$2:$I$2,""),"") and set with CTRL-SHIFT-ENTER. further IF criteria can be added, ifneeded.

[wiewie002]

Dear Berlan and Glenn,
That great. thank you for your help.

Regards,
Wie