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.

Answer 1

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).

Answer 2

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

Answer 3

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.

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 = "Hello world", GetTrueBlank, A1)

Step 1. Put this code in Module of VBA.

Function Delete_UDF(rng)
    ThisWorkbook.Application.Volatile
    rng.Value = ""
End Function

Step 2. In Sheet1 in A1 cell add named range GetTrueBlank with the following formula:

=EVALUATE("Delete_UDF("&CELL("address",Sheet1!A1)&")")

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. It opens in Google Sheets and will not work there. Download it first to get xlsm file work in Excel.

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.

Answer 4

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>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.

Answer 5

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"@
  

Answer 6

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.

1. I did a "copy" of all the data, and pasted the data as "values".
2. 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.
3. 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 & 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.

Answer 7

Use COUNTBLANK(B1)>0 instead of ISBLANK(B1) inside your IF statement.

Unlike ISBLANK(), COUNTBLANK() considers "" as empty and returns 1.

Answer 8

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.

Answer 9

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.

Answer 10

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

Answer 11

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)

Answer 12

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

Answer 13

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...

Answer 14

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.

Answer 15

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.

Answer 16

If you are, like me, after an empty cell so that the text in a cell can overflow to an adjacent cell, return "" but set the cell format text direction to be rotated by a few degrees. If you align left, you will find this causes the text to spill to an adjacent cell as if that cell were empty.

See before and after:

=IF(RANDARRAY(2,10,1,10,TRUE)>8,"abcdefghijklmnopqrstuvwxyz","")

Note the empty cells are not empty but contain "", yet the text can still spill.

Answer 17

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 &. 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.

Answer 18

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="'","",TEXT(F2,""))

Answer 19

This can be done in Excel, without using the new chart feature of setting #N/A to be a gap. But it’s fiddly. Let’s say that you want to make line on an XY chart. Then:
Row 1: point 1
Row 2: point 2
Row 3: hard empty
Row 4: point 2
Row 5: point 3
Row 6: hard empty
Row 7: point 3
Row 8: point 4
Row 9: hard empty
etc

The result is a lot of separate lines. The formula for the points can control whether omitted by a #N/A. Typically the formulae for the points INDEX() into another range.

Answer 20

I also found this through Google, and found a solution not in returning a true null value (because that can't be done) and not via VBA (because the web-based Excel365 doesn't support VB) but via IFERROR handling in the downstream function.

My own issue was in array operations over a range of formula cells where only a subset were "active". Each cell in the range returned a numeric value if it was active, and a ""empty string otherwise, e.g. each having some formula like this from my cell AK32 in it: = IF(ISNUMBER(AG32), MAX(AG32,MIN(12,AI32)), "") -- please don't critique this, it's an illustrative minimal version of the real one.

The problem is that if I then aggregate those conditionally calculated cells in row 32, and try to sum them, I get #VALUE! errors should there be any "" results due to empty optional inputs in e.g. AG32. If they'd been literal empty cells, that would have been fine, but a "" is neither empty nor numeric.

This issue can be handled in my downstream aggregation function by combination of VALUE() and ISERROR(): try to convert/cast the range to be numeric-valued, catch the error-cases, and replace them with zeroes: = SUM( IFERROR(VALUE(AK32:OQ32),0) ). [To anticipate "helpful" comments, I've elided a bunch of FILTER() stuff that picks the right cells out of that AK32:OQ32 range. Treat it as if I really want to sum everything in that contiguous range.]

I pine for some more modern, Numpy/Pandas type of array-operation language to be usable in (web) Excel formulae, but at least this one is possible.

Answer 21

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), "")