Thread: If cell has x turn into number and concatenate

Example is in the attch:

Book11.xls

This formula works but if there is no number 4 then I don't want comma after 1, or 2, or 3,

How avoid it:

results

1,2,3,4
1,2,
1,2,3,
3,4
1,
4
2,3,

(red , need to be removed)

Of course, you can change formula at will.

take the , out of the formula?
oh i see you want 1,2 and 1,2,3
but no trailing ,
=LEFT(IF(A2="x","1,","")&IF(B2="x","2,","")&IF(C2="x","3,","")&IF(D2="x","4,",""),LEN(IF(A2="x","1," ,"")&IF(B2="x","2,","")&IF(C2="x","3,","")&IF(D2="x","4,",""))-1)
there must be a shorter way tho.

Just after last number

there you go, add , after every number then remove last character

I figure it out during lunch

=LEFT(IF(A3="x";"1,";"")&IF(B3="x";"2,";"")&IF(C3="x";"3,";"")&IF(D3="x";"4";"");2*COUNTA(A3:D3)-1)