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

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

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 4

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

Answer 5

It seems like a simple answer that works has been missed.

Name an empty cell in your spreadsheet BlankCell.

Return BlankCell in your formula, e.g.

=ISBLANK(IFNA(VLOOKUP(A2,SomeTable,2,False),BlankCell))

This will return TRUE if ISBLANK(VLOOKUP(A2,SomeTable,2,False)) is TRUE.

Answer 6

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 I found in the collection of FrankensTeam (reference below).

After setting up 3 steps, you will be able to use named range GetTrueBlank, just as you want in your answer:

=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

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.

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