<>0 formula with blank cells

NJS1982

Board Regular
Joined
Sep 24, 2009
Messages
183
Office Version
  1. 365
Platform
  1. Windows
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?
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
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:
[COLOR=#333333]=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,"")),"")[/COLOR]

Infact your above code seems pretty fine.
 
Upvote 0
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)
 
Last edited:
Upvote 0
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,"")),"")
 
Upvote 0
Perfect, many thanks Peter, the first part worked perfectly.
Good news. :)


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))
 
Upvote 0
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!
 
Upvote 0
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!
:confused: 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. :)
 
Upvote 0
:confused: 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!
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,192
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top