Excel formula concat text in excel without zero values-CodePudding

I am new to excel formulas. How to skip the zeros and the show result in image format with the yellow colour columns in the excel formula?

CodePudding user response：

If the results are not always in ascending order (if they are @Solar Mike has already solved it for you with MAX) then perhaps:

=A2&" "&LOOKUP(2,1/(B2:D2<>0),B2:D2)

CodePudding user response：

As per your sample data it seems simple below formula should work.

=A2&" "&MAX(B2:D2)

Or can try-

=A2 & " " & XLOOKUP(1000,B2:D2,B2:D2,,-1,-1)

and if you want actually non zero right cell data (including text data) then use FILTERXML() like-

=A2& " " & FILTERXML("<t><s>"&TEXTJOIN("</s><s>",TRUE,FILTER(B2:D2,B2:D2>0))&"</s></t>","//s[last()]")

CodePudding user response：

Looking at your results however, then this:

Something like:

=if(A2>0,A2&" ","")&if(B2>0,B2&" ","")&if(C2>0,C2&" ","")&if(D2>0,D2&" ","")

will work. Probably could be made shorter... And should work with all versions of Excel.