Empty string vs cell format

If I test for the empty string on a cell that is empty and its format is (default) general or text, I would expect this test to be true. However, if I change the format of the empty cell to number format, how come it still succeeds? It seems like it should fail because the cell isn't actually a string type but a number type. Similarly, if I do an if 2 = "2", this also works and this should through an error I would think. How is Excel interpreting these types of comparisons?

An empty cell is just that, empty. The format simply changes the way a cell is viewed, it does not change the value(if any) of the cell.
So if A1 is empty then =ISBLANK(A1) will return true regardless of the format.
I don't understand what you mean by

Similarly, if I do an if 2 = "2", this also works

Originally Posted by MrShorty

Excel formulas do not look at cell formats, but the value contained in the cell. Any blank cell (assuming truly blank and not some formula that returns "") will be interpreted as 0 or "", depending on the formula doing the comparison. So if A1 is blank, =A1="" and =A1=0 will both return TRUE -- and the number format will not impact this result.

I'm not sure what you mean by 2="2" works. When I put =2="2" into a cell, I get FALSE, because Excel sees the number 2 as something different than the text string "2". If I format a pair of cells as "0.00" and enter 2 into one and '2 (text 2) into another, and enter =A1=A2, I also get FALSE. The number format does not change how Excel evaluates the resulting boolean expression.

I don't know if it helps, but I think a lot of people get confused by this when dates are involved. Enter the number 42308 into a pair of cells and format one as a date format ("m/d/yy" maybe). =A1=A2 will still return TRUE, even though one cell is formatted as a date and the other is formatted as a number.

The key point is that Excel does not use number formatting when deciding how to evaluate formulas. It uses the value stored in the cell. Empty is interpreted as either 0 or "", depending on the formula. Does that help answer your question?

MrShorty - Thank you this was helpful. What I meant by 2 = "2" is that if you use an If statement in VBA comparing those two values, it returns true. Shouldn't that throw an error because you cannot compare two different data types or does it convert one to the other in order to compare them? I would think other stronger typed languages would give a compile time error for this.

Originally Posted by Fluff13

An empty cell is just that, empty. The format simply changes the way a cell is viewed, it does not change the value(if any) of the cell.
So if A1 is empty then =ISBLANK(A1) will return true regardless of the format.
I don't understand what you mean by

Fluff13 - Thank you. See below regarding my comment about 2 = "2".

You should not get a compile error when comparing different values, you will simply get a boolean value.
If you enter 2 into A1 & '2 into B1 & run this

Please Login or Register  to view this content.

It will return FALSE as one is a number & the other is text.

Originally Posted by Fluff13

You should not get a compile error when comparing different values, you will simply get a boolean value.
If you enter 2 into A1 & '2 into B1 & run this

Please Login or Register  to view this content.

It will return FALSE as one is a number & the other is text.

Hmmm, if you step through the code below, it returns true...

Please Login or Register  to view this content.

That's true, although I don't know why, but why would you want to do that?