Return empty cell from formula in Excel

Question

I need to return an empty cell from an Excel formula  but it appears that Excel treats an empty string or a reference to an empty cell differently than a true empty cell   So essentially I need something like   IF some condition EMPTY   some value    I tried to do things such as   IF some condition    some value    and   IF some condition  some value    and assuming B1 is an empty cell   IF some condition B1 some value    but none of these appear to be true empty cells  I m guessing because they are the result of a formula   Is there any way to populate a cell if and only if some condition is met and otherwise keep the cell truly empty   EDIT  as recommended  I tried to return NA    but for my purposes this did not work either   Is there a way to do this with VB   EDIT  I am building a worksheet that pulls in data from other worksheets that is formatted to the very specific demands of an application that imports the data into a database   I do not have access to change the implementation of this application  and it fails if the value is    instead of actually empty

User · Answer

This answer does not fully deal with the OP, but there are have been several times I have had a similar problem and searched for the answer.

If you can recreate the formula or the data if needed (and from your description it looks as if you can), then when you are ready to run the portion that requires the blank cells to be actually empty, then you can select the region and run the following vba macro.

Sub clearBlanks()
    Dim r As Range
    For Each r In Selection.Cells
        If Len(r.Text) = 0 Then
            r.Clear
        End If
    Next r
End Sub

this will wipe out off of the contents of any cell which is currently showing "" or has only a formula

User · Answer

Wow  an amazing number of people misread the question  It s easy to make a cell look empty  The problem is that if you need the cell to be empty  Excel formulas can t return  no value  but can only return a value  Returning a zero  a space  or even    is a value   So you have to return a  magic value  and then replace it with no value using search and replace  or using a VBA script  While you could use a space or     my advice would be to use an obvious value  such as  NODATE  or  NONUMBER  or  XXXXX  so that you can easily see where it occurs - it s pretty hard to find    in a spreadsheet

User · Answer

I used the following work around to make my excel looks cleaner   When you make any calculations the    will give you error so you want to treat it as a number so I used a nested if statement to return 0 istead of     and then if the result is 0 this equation will return       IF  IF A5    0 A5  IF B5    0 B5     0      IF A5    0 A5  IF B5    0 B5      This way the excel sheet will look clean

User · Answer

Try evaluating the cell using LEN  If it contains a formula LEN will return 0  If it contains text it will return greater than 0

User · Answer

If you are using lookup functions like HLOOKUP and VLOOKUP to bring the data into your worksheet place the function inside brackets and the function will return an empty cell instead of a  0    For Example   This will return a zero value if lookup cell is empty          HLOOKUP  Lookup Value  Array ROW FALSE    This will return an empty cell if lookup cell is empty           HLOOKUP  Lookup Value  Array ROW FALSE     I don t know if this works with other functions   I haven t tried   I am using Excel 2007 to achieve this   Edit  To actually get an IF A1         to come back as true there needs to be two lookups in the same cell seperated by an  amp    The easy way around this is to make the second lookup a cell that is at the end of the row and will always be empty

User · Answer

Well so far this is the best I could come up with   It uses the ISBLANK function to check if the cell is truly empty within an IF statement  If there is anything in the cell  A1 in this example  even a SPACE character   then the cell is not EMPTY and the calculation will result  This will keep the calculation errors from showing until you have numbers to work with   If the cell is EMPTY then the calculation cell will not display the errors from the calculation If the cell is NOT EMPTY then the calculation results will be displayed  This will throw an error if your data is bad  the dreaded  DIV 0    IF ISBLANK A1      STDEV B5 B14     Change the cell references and formula as you need to

User · Answer

Yes  it is possible  It is possible to have a formula returning a trueblank if a condition is met  It passes the test of the ISBLANK formula  The only inconvenience is that when the condition is met  the formula will evaporate  and you will have to retype it  You can design a formula immune to self-destruction by making it return the result to the adjacent cell  Yes  it is also possible  I refer you to this solution at the end of my answer   All you need is to set up a named range  say GetTrueBlank  and you will be able to use the following pattern just like in your question   IF A1    quot Hello world quot   GetTrueBlank  A1   Step 1  Put this code in Module of VBA  Function Delete UDF rng      ThisWorkbook Application Volatile     rng Value    quot  quot  End Function  Step 2  In Sheet1 in A1 cell add named range GetTrueBlank with the following formula   EVALUATE  quot Delete UDF  quot  amp CELL  quot address quot  Sheet1 A1  amp  quot   quot     That s it  There are no further steps  Just use self-annihilating formula  Put in the cell  say B2  the following formula   IF A2 0 GetTrueBlank A2   The above formula in B2 will evaluate to trueblank  if you type 0 in A2  You can download a demonstration file here  In the example above  evaluating the formula to trueblank results in an empty cell  Checking the cell with ISBLANK formula results positively in TRUE  This is hara-kiri  The formula disappears from the cell when a condition is met  The goal is reached  although you probably might want the formula not to disappear  You may modify the formula to return the result in the adjacent cell so that the formula will not kill itself  See how to get UDF result in the adjacent cell   I have come across the examples of getting a trueblank as a formula result revealed by The FrankensTeam here  https   sites google com site e90e50 excel-formula-to-change-the-value-of-another-cell

User · Answer

This is how I did it for the dataset I was using   It seems convoluted and stupid  but it was the only alternative to learning how to use the VB solution mentioned above    I did a  copy  of all the data  and pasted the data as  values   Then I highlighted the pasted data and did a  replace   Ctrl-H  the empty cells with some letter  I chose q since it wasn t anywhere on my data sheet  Finally  I did another  replace   and replaced q with nothing      This three step process turned all of the  empty  cells into  blank  cells    I tried merging steps 2  amp  3 by simply replacing the blank cell with a blank cell  but that didn t work--I had to replace the blank cell with some kind of actual text  then replace that text with a blank cell

User · Answer

You re going to have to use VBA  then   You ll iterate over the cells in your range  test the condition  and delete the contents if they match    Something like   For Each cell in SomeRange   If  cell value   SomeTest  Then cell ClearContents Next

User · Answer

The answer is positively - you can not use the  IF   function and leave the cell empty   Looks empty  is not the same as empty  It is a shame two quotation marks back to back do not yield an empty cell without wiping out the formula

User · Answer

Excel does not have any way to do this    The result of a formula in a cell in Excel must be a number  text  logical  boolean  or error  There is no formula cell value type of  empty  or  blank    One practice that I have seen followed is to use NA   and ISNA    but that may or may not really solve your issue since there is a big differrence in the way NA   is treated by other functions  SUM NA    is  N A while SUM A1  is 0 if A1 is empty

User · Answer

Use COUNTBLANK B1  gt 0 instead of ISBLANK B1  inside your IF statement    Unlike ISBLANK    COUNTBLANK   considers    as empty and returns 1

User · Answer

I was stripping out single quotes so a telephone number column such as  1-800-123-4567 didn t result in a computation and yielding a negative number  I attempted a hack to remove them on empty cells  bar the quote  then hit this issue too  column F   It s far easier to just call text on the source cell and voila    IF F2  quot   quot   quot  quot  TEXT F2  quot  quot

User · Answer

Google brought me here with a very similar problem  I finally figured out a solution that fits my needs  it might help someone else too     I used this formula    IFERROR MID Q2  FIND     Q2   FIND     Q2  - FIND     Q2    1

User · Answer

Maybe this is cheating  but it works   I also needed a table that is the source for a graph  and I didn t want any blank or zero cells to produce a point on the graph  It is true that you need to set the graph property  select data  hidden and empty cells to  show empty cells as Gaps   click the radio button   That s the first step   Then in the cells that may end up with a result that you don t want plotted  put the formula in an IF statement with an NA   results such as  IF  A8 gt TODAY   NA     formula to be plotted    This does give the required graph with no points when an invalid cell value occurs  Of course this leaves all cells with that invalid value to read  N A  and that s messy   To clean this up  select the cell that may contain the invalid value  then select conditional formatting - new rule  Select  format only cells that contain  and under the rule description select  errors  from the drop down box  Then under format select font - colour - white  or whatever your background colour happens to be   Click the various  buttons to get out and you should see that cells with invalid data look blank  they actually contain  N A but white text on a white background looks blank   Then the linked graph also does not display the invalid value points

User · Answer

So many answers that return a value that LOOKS empty but is not actually an empty as cell as requested     As requested  if you actually want a formula that returns an empty cell  It IS possible through VBA  So  here is the code to do just exactly that  Start by writing a formula to return the  N A error wherever you want the cells to be empty  Then my solution automatically clears all the cells which contain that  N A error  Of course you can modify the code to automatically delete the contents of cells based on anything you like   Open the  visual basic viewer   Alt   F11   Find the workbook of interest in the project explorer and double click it  or right click and select view code   This will open the  view code  window  Select  Workbook  in the  General  dropdown menu and  SheetCalculate  in the  Declarations  dropdown menu   Paste the following code  based on the answer by J T  Grimes  inside the Workbook SheetCalculate function      For Each cell In Sh UsedRange Cells         If IsError cell Value  Then             If  cell Value   CVErr xlErrNA   Then cell ClearContents         End If     Next   Save your file as a macro enabled workbook  NB  This process is like a scalpel  It will remove the entire contents of any cells that evaluate to the  N A error so be aware  They will go and you cant get them back without reentering the formula they used to contain   NB2  Obviously you need to enable macros when you open the file else it won t work and  N A errors will remain undeleted

User · Answer

What I used was a small hack  I used T 1   which returned an empty cell  T is a function in excel that returns its argument if its a string and an empty cell otherwise  So  what you can do is    IF condition T 1  value

User · Answer

If the goal is to be able to display a cell as empty when it in fact has the value zero  then instead of using a formula that results in a blank or empty cell  since there s no empty   function  instead    where you want a blank cell  return a 0 instead of    and THEN set the number format for the cells like so  where you will have to come up with what you want for positive and negative numbers  the first two items separated by semi-colons   In my case  the numbers I had were 0  1  2    and I wanted 0 to show up empty   I never did figure out what the text parameter was used for  but it seemed to be required    0 0     text

[excel] Return empty cell from formula in Excel

Examples related to excel

Examples related to excel-formula