When is a Blank cell not Blank? - XL07 1

[PeterMoran]

Hi,

I have a formula in B1 which says:

=IF(A1="x","","x")

Now I would assume that if A1 is "x" then B1 would be blank - Not So!

If I use F5 Special and select Blanks on this cell (B1), it does not show as a Blank even though it has a length of 0!

However if I delete the formula, it does show as Blank.

What do I need to do in the formula for it to test successfully as a Blank?

 

[SkipVought]

Hi,

BLANK means that NOTHING is in the cell.

Your cell is not BLANK because it has a formula in it!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

...but here's what you might do.

Seems that you might have a LIST in column A. If so,
1) insert a row for a heading,
2) enter a heading in A1,
3) turn on Filter (Data > Sort & Filter > Filter).

Then show (Blanks).

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[PeterMoran]

Hi Skip and any other Readers,

Thanks for your Nuances!

Please tell me then why the formula works for any other value than "" - if I change it to a space " " or any other value it works perfectly as I would expect.

I don't intrinsically understand why "" should be any different to "a" or "b" or " ".

Thanks in anticipation.

Peter Moran

 

[SkipVought]

I don't intrinsically understand why "" should be any different to "a" or "b" or " ".

Well "a" or "b" or " " are all characters, while "" is no character. While "a" or "b" or " " have a length of 1, "" has a length of zero. But for the case of F5, "" is not equivalent to BLANK because there is SOMETHING (a FORMULA) in the cell that is distinctively different than BLANK (NOTHING)

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[kjv1611]

Who's on first?!

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[SkipVought]

I don't know.

He's on third!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]