Return empty cell from formula in Excel
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.
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
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).
Yes, it is possible.
It is possible to have an annihilating formula evaluating to trueblank if condition is met. It passes the test of ISBLANK formula. This seemingly impossible trick can be done in a few different ways. Here goes my favorite example of The FrankensTeam collection.
You will be able to use for example:
=IF(A1="Hello world",GetTrueBlank,A1)
after defining GetTrueBlank as named range .
Step 1. Put this code in Module.
Function Delete_UDF(rng)
ThisWorkbook.Application.Volatile
rng.Value = ""
End Function
Two remarks about VBA code. (1) Do not be mislead by rng.Value="" Named rage GetTrueBlank will finally be a trueblank, not an empty string like in double quote ="" . (2) In my tests, the first line of code ThisWorkbook.Application.Volatile originally used by The FrankensTeam proved to be unnecessary.
Step 2. In Sheet1 in A1 cell add named range GetTrueBlank with the following formula:
=EVALUATE("Delete_UDF("&CELL("address",Sheet1!A1)&")")
Step 3. Use self annihilating formula. Put in 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 and all the other examples by The FrankensTeam, evaluating formula to trueblank results in an empty cell. Checking result with ISBLANK formula results positively in TRUE. These are hara-kiri like formulas. The formula disappears from cell when condition is met. The goal is reached, although the author of the question might want the formula not to disappear.
Note that these examples might be modified to give results in adjacent cell, not killing itself. But that is another issue on how to get the formula results in a different cell then formula itself.
I have come accross the examples of getting a trueblank as a formula result reaveled by The FrankensTeam here: https://sites.google.com/site/e90e50/excel-formula-to-change-the-value-of-another-cell
链接地址: http://www.djcxy.com/p/60778.html上一篇: 自动求和为相同的类型
下一篇: 在Excel中从公式返回空单元格