Thread: <>0 formula with blank cells Thanks: 0 Likes: 0

1. <>0 formula with blank cells

Hi all, I have a formula that looks like this:

=IFNA(IF(A3="","",IF(AX3<>0,AX3&" / ","")&IF(AY3>0,AY3&" / ","")&IF(AZ3<>0,AZ3&" / ","")&IF(BA3<>0,BA3&" / ","")&IF(BB3<>0,BB3&" / ","")&IF(BC3<>0,BC3&" / ","")&IF(BD3<>0,BD3&" / ","")&IF(BE3<>0,BE3,"")),"")

The cells it is looking at - AX3,AY3,AZ3 etc. are all cells that contain a VLookup formula. Sometimes a value is displayed in these cells, sometimes it is blank (though not truly blank as it has the formula in it).

All I want the formula I have to do is say 'If cell AX3 is not blank, provide the value in cell AX3 with a / at the end, otherwise keep it blank".

However, it seems to pick up the 'blank' contents of of cell AX3, so it looks like this '/'.

Anyone know what I am doing wrong?

2. Re: <>0 formula with blank cells

I checked the below code, if cell AX3 is blank, i am not getting SLASH("/") result but when is try this with (IF(ISBLANK) function i am getting SLASH("/") result.

Code:
`=IFNA(IF(A3="","",IF(AX3<>0,AX3&" / ","")&IF(AY3>0,AY3&" / ","")&IF(AZ3<>0,AZ3&" / ","")&IF(BA3<>0,BA3&" / ","")&IF(BB3<>0,BB3&" / ","")&IF(BC3<>0,BC3&" / ","")&IF(BD3<>0,BD3&" / ","")&IF(BE3<>0,BE3,"")),"")`
Infact your above code seems pretty fine.

3. Re: <>0 formula with blank cells

I believe that the adjustment to your formula simply requires all those <>0 parts of your formula to be changed to <>""
So it would become
=IFNA(IF(A3="","",IF(AX3<>"",AX3&" / ","")&IF(AY3<>"",AY3&" / ","")&IF(AZ3<>"",AZ3&" / ","")&IF(BA3<>"",BA3&" / ","")&IF(BB3<>"",BB3&" / ","")&IF(BC3<>"",BC3&" / ","")&IF(BD3<>"",BD3&" / ","")&IF(BE3<>"",BE3,"")),"")

However, from looking at the last part of your formula, I am wondering if you are trying to avoid a " / " after whatever is the last value in the list. If that is so, and you have a recent version of Excel that has the TEXTJOIN function, you could do it with this much shorter formula.

=TEXTJOIN(" / ",TRUE,AX3:BE3)

4. Re: <>0 formula with blank cells

Perfect, many thanks Peter, the first part worked perfectly.

With the TEXTJOIN part though; I don't understand what I need to amend within my formula - can you please show me?

=IFNA(IF(A12="","",IF(AX12<>"",AX12&" / ","")&IF(AY12<>"",AY12&" / ","")&IF(AZ12<>"",AZ12&" / ","")&IF(BA12<>"",BA12&" / ","")&IF(BB12<>"",BB12&" / ","")&IF(BC12<>"",BC12&" / ","")&IF(BD12<>"",BD12&" / ","")&IF(BE12<>"",BE12,"")),"")

5. Re: <>0 formula with blank cells

Originally Posted by NJS1982
Perfect, many thanks Peter, the first part worked perfectly.
Good news.

Originally Posted by NJS1982
With the TEXTJOIN part though; I don't understand what I need to amend within my formula - can you please show me?
If you have the TEXTJOIN function then try replacing that whole long formula with this one & see if it does what you want.

=IF(A3="","",TEXTJOIN(" / ",TRUE,AX3:BE3))

6. Re: <>0 formula with blank cells

Great, many thanks!!

7. Re: <>0 formula with blank cells

Originally Posted by NJS1982
Great, many thanks!!
You're welcome.

Did you get the TEXTJOIN option going?

8. Re: <>0 formula with blank cells

It did, thanks. I just had to add in some extra columns and hide my cell with the longer original formula, presenting the user with just the TEXTJOIN result. Thanks again!

9. Re: <>0 formula with blank cells

Originally Posted by NJS1982
It did, thanks. I just had to add in some extra columns and hide my cell with the longer original formula, presenting the user with just the TEXTJOIN result. Thanks again!
I don't really understand that as the idea of the TEXTJOIN function was to completely replace the longer original formula.

Anyway, if you have something that you are happy with that's fine.

10. Re: <>0 formula with blank cells

Originally Posted by Peter_SSs
I don't really understand that as the idea of the TEXTJOIN function was to completely replace the longer original formula.

Anyway, if you have something that you are happy with that's fine.
Ahhh, I think I was over complicating it! Taken another look this morning and you are right, goodbye big long formula and hello your snappy short one. Thanks!