CODEWITHSUNDEEP
excelexcel-formulaconcatenationformula
Pradeep Rao M
Pradeep Rao M

Reputation: 23

Excel formula concat text in excel without zero values

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?

enter image description here

Upvotes: 0

Views: 367

Answers (4)

Bud
Bud

Reputation: 11

In one single Cell:

Cell E2 =TEXTJOIN(" ";TRUE;IF(A2:D2=0;"";A2:D2))

Or if you are looking from min max only:

cell E2 = MIN(a2:d2)&" "&MAX(a2:d2)

Upvotes: 0

Harun24hr
Harun24hr

Reputation: 36880

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()]")

Upvotes: 1

Rory
Rory

Reputation: 34055

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)

Upvotes: 1

Solar Mike
Solar Mike

Reputation: 8375

Looking at your results however, then this:

enter image description here 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.

Ba

Upvotes: 0

Related Questions