<>0 formula with blank cells

NJS1982

Board Regular
Joined
Sep 24, 2009
Messages
179
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?
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Sam_D_Ben

Active Member
Joined
Oct 17, 2012
Messages
379
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.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
45,283
Office Version
365
Platform
Windows
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:

NJS1982

Board Regular
Joined
Sep 24, 2009
Messages
179
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,"")),"")
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
45,283
Office Version
365
Platform
Windows
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))
 

NJS1982

Board Regular
Joined
Sep 24, 2009
Messages
179
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!
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
45,283
Office Version
365
Platform
Windows
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. :)
 

NJS1982

Board Regular
Joined
Sep 24, 2009
Messages
179
: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!
 

Watch MrExcel Video

Forum statistics

Threads
1,102,844
Messages
5,489,221
Members
407,681
Latest member
HoneyBadger914

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top