Nested IF and LEFT functions

[kyleno]

I'm not entirely sure how to nest a string of IF functions with a LEFT function.

My IF function is as follows (located in cell W2):
=IF(I2<>0,I2&", ", "")&IF(J2<>0,J2&", ", "")&IF(K2<>0,K2&", ", "")&IF(L2<>0,L2&", ", "")&IF(M2<>0,M2&", ", "")&IF(N2<>0,N2&", ", "")&IF(O2<>0,O2&", ", "")&IF(P2<>0,P2&", ", "")&IF(Q2<>0,Q2&", ", "")&IF(R2<>0,R2&", ", "")&IF(S2<>0,S2&", ", "")&IF(T2<>0,T2&", ", "")&IF(U2<>0,U2,"")

My issue is that a comma appears at the end of the result if there is no value contained in cell U2. I need a dynamic formula that live updates, NOT a macro.

I searched and did find a LEFT function that I thought might work to alleviate this problem (in cell X2):
=LEFT(W2,LEN(W2)-(RIGHT(W2)=","))

This did NOTremove the comma.

It seems to me that I would need a nested LEFT formula to go along with my IF formula. I attempted to add the above LEFT formula to the beginning or end, but I received a circular reference error.

Anyone have any thoughts?

 

[Scott T]

Not very pretty put it seems to work

=IF(RIGHT(TRIM(IF(I2<>0,I2&", ", "")&IF(J2<>0,J2&", ", "")&IF(K2<>0,K2&", ", "")&IF(L2<>0,L2&", ", "")&IF(M2<>0,M2&", ", "")&IF(N2<>0,N2&", ", "")&IF(O2<>0,O2&", ", "")&IF(P2<>0,P2&", ", "")&IF(Q2<>0,Q2&", ", "")&IF(R2<>0,R2&", ", "")&IF(S2<>0,S2&", ", "")&IF(T2<>0,T2&", ", "")&IF(U2<>0,U2,"")),1)<>",",TRIM(IF(I2<>0,I2&", ", "")&IF(J2<>0,J2&", ", "")&IF(K2<>0,K2&", ", "")&IF(L2<>0,L2&", ", "")&IF(M2<>0,M2&", ", "")&IF(N2<>0,N2&", ", "")&IF(O2<>0,O2&", ", "")&IF(P2<>0,P2&", ", "")&IF(Q2<>0,Q2&", ", "")&IF(R2<>0,R2&", ", "")&IF(S2<>0,S2&", ", "")&IF(T2<>0,T2&", ", "")&IF(U2<>0,U2,"")),SUBSTITUTE(TRIM(IF(I2<>0,I2&", ", "")&IF(J2<>0,J2&", ", "")&IF(K2<>0,K2&", ", "")&IF(L2<>0,L2&", ", "")&IF(M2<>0,M2&", ", "")&IF(N2<>0,N2&", ", "")&IF(O2<>0,O2&", ", "")&IF(P2<>0,P2&", ", "")&IF(Q2<>0,Q2&", ", "")&IF(R2<>0,R2&", ", "")&IF(S2<>0,S2&", ", "")&IF(T2<>0,T2&", ", "")&IF(U2<>0,U2,"")),",","",SUMPRODUCT(--(I2:U2<>""))))

 

[kyleno]

Brilliant! That worked perfectly. Thank you so much! Love the Space Balls quote btw

Not very pretty put it seems to work

=IF(RIGHT(TRIM(IF(I2<>0,I2&", ", "")&IF(J2<>0,J2&", ", "")&IF(K2<>0,K2&", ", "")&IF(L2<>0,L2&", ", "")&IF(M2<>0,M2&", ", "")&IF(N2<>0,N2&", ", "")&IF(O2<>0,O2&", ", "")&IF(P2<>0,P2&", ", "")&IF(Q2<>0,Q2&", ", "")&IF(R2<>0,R2&", ", "")&IF(S2<>0,S2&", ", "")&IF(T2<>0,T2&", ", "")&IF(U2<>0,U2,"")),1)<>",",TRIM(IF(I2<>0,I2&", ", "")&IF(J2<>0,J2&", ", "")&IF(K2<>0,K2&", ", "")&IF(L2<>0,L2&", ", "")&IF(M2<>0,M2&", ", "")&IF(N2<>0,N2&", ", "")&IF(O2<>0,O2&", ", "")&IF(P2<>0,P2&", ", "")&IF(Q2<>0,Q2&", ", "")&IF(R2<>0,R2&", ", "")&IF(S2<>0,S2&", ", "")&IF(T2<>0,T2&", ", "")&IF(U2<>0,U2,"")),SUBSTITUTE(TRIM(IF(I2<>0,I2&", ", "")&IF(J2<>0,J2&", ", "")&IF(K2<>0,K2&", ", "")&IF(L2<>0,L2&", ", "")&IF(M2<>0,M2&", ", "")&IF(N2<>0,N2&", ", "")&IF(O2<>0,O2&", ", "")&IF(P2<>0,P2&", ", "")&IF(Q2<>0,Q2&", ", "")&IF(R2<>0,R2&", ", "")&IF(S2<>0,S2&", ", "")&IF(T2<>0,T2&", ", "")&IF(U2<>0,U2,"")),",","",SUMPRODUCT(--(I2:U2<>""))))