ISBLANK not working/malfunctioning

[Excel Chimp]

I'm going mad trying to figure out the issue. ISBLANK is returning FALSE for a seemingly blank cell. The formula is:
ISBLANK(VLOOKUP($A13,data1,COLUMN(C:C),TRUE)

I thought it has something to do with the VLOOKUP returned value. However,when I press delete on cell A13, the problem is fixed. Strange?!
Running just the isblank function on cell A13 indicates a FALSE value. I just can't figure out what is in the cell or how to remove it.

Please give me your advice.

 

[Excel Chimp]

Anyone?

 

[Scott Huish]

Did you do a Copy Paste Special Values on a formula that returned "".
If you did then the cell contains the value of an empty string and therefore not blank.

Try using a check of = ""

 

[shift-del]

Hello

What do ISTEXT() and CODE() return?

 

[Excel Chimp]

Thanks for replying. I did try to search in Find and Replace: ""

Nothing found.

 

[Excel Chimp]

Hello

What do ISTEXT() and CODE() return?

ISTEXT returns TRUE. CODE returns #VALUE!.
When I use F5 key to select blanks, the source cell is not highlighted, indicating it is NOT blank.

 

[shift-del]

ISTEXT returns TRUE. CODE returns #VALUE!.
When I use F5 key to select blanks, the source cell is not highlighted, indicating it is NOT blank.

Then HOTPEPPER is on the right track. It's a formula-blank copied to a value.

It can be identified with the combination of:
ISBLANK()=FALSE
ISTEXT()=TRUE
CODE()=#VALUE!
LEN()=0

 

[pgc01]

ISBLANK is returning FALSE for a seemingly blank cell.

Hi

I think the best would be to avoid the word blank altoguether. Excel made a mess with this word, as it uses it with different meanings in the help and even in the functions names.

Check the beginning of post #6 in this thread

http://www.mrexcel.com/forum/showthread.php?p=2128495

I hope it helps to clarify this issue.

 

[Excel Chimp]

Thanks tremendously for the help. Really, this one was causing me brain damage!
Is the best way to locate the null cells (proper term?) some combination of the
Formulas mentioned above? Like an IF=(And(istext). Etc
?

 

[SamParsons]

Then HOTPEPPER is on the right track. It's a formula-blank copied to a value.

It can be identified with the combination of:
ISBLANK()=FALSE
ISTEXT()=TRUE
CODE()=#VALUE!
LEN()=0

FWIW - you must be very careful with the Len() method. I just tried Len(a formula blank cell) and the outcome was 1 due to the space in my if statement.

To be clear:

WHEN LEN = 1
if A1 had the following formula: =if(isna(vlookup(blah)," ",vlookup(blah))
the value showing in A1 was: " "
=len(A1) would resolve to: 1

WHEN LEN = 0
if A1 had the following formula: =if(isna(vlookup(blah),"",vlookup(blah))
the value showing in A1 was: ""
=len(A1) would resolve to: 0

It's a good clarification to add!

Hope that helps!