ISBLANK not working/malfunctioning

Excel Chimp

Board Regular
Joined
Oct 30, 2008
Messages
90
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.
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Aug 28, 2009
Messages
516
Office Version
  1. 365
Platform
  1. Windows
Hello

What do ISTEXT() and CODE() return?
 

Excel Chimp

Board Regular
Joined
Oct 30, 2008
Messages
90

ADVERTISEMENT

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

Nothing found.
 

shift-del

Well-known Member
Joined
Aug 28, 2009
Messages
516
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
 

Excel Chimp

Board Regular
Joined
Oct 30, 2008
Messages
90
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
?
:cool:
 

SamParsons

New Member
Joined
Jul 15, 2014
Messages
15
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!
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,123,177
Messages
5,600,160
Members
414,367
Latest member
dw970906

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top