Isblank function erroneous results

Trevarrick

New Member
Joined
Sep 1, 2009
Messages
10
Using data of unknown origin in Excel I found Isblank was giving a FALSE result on some apparently blank cells while giving a TRUE result on others.

Even if I used TRIM and CLEAN functions on the offending data and pasted the resulting values back the the original locations, the Isblank result was still FALSE. Also, the font colour was not set to white or transparent.

However, if I selected the cell with the 'invisible' data, clicked in the Formula Bar and pressed Enter without entering any new data, the problem disappeared for that cell. The problem also disappeared if I selected the problem 'blank' cell and pressed delete.

Can anyone explain this 'invisible data' and tell me how I can detect it using a function or formula?
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,350
Use CODE function on the offending cells to give you the ASCII value of the character(s) so you can work out why this happened
 

Seti

Well-known Member
Joined
May 19, 2002
Messages
2,916
Here's one thing to try. Assuming the invisible data is in A1, what does =LEN(A1) return? If you don't get a 0, what does =CODE(LEFT(A1),1) give you?
 

Trevarrick

New Member
Joined
Sep 1, 2009
Messages
10
Use CODE function on the offending cells to give you the ASCII value of the character(s) so you can work out why this happened
Code function gives <TABLE style="WIDTH: 62pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=83><COLGROUP><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 3035" width=83><TBODY><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 62pt; HEIGHT: 12pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 height=16 width=83>#VALUE!</TD></TR></TBODY></TABLE>
 

Trevarrick

New Member
Joined
Sep 1, 2009
Messages
10

ADVERTISEMENT

Here's one thing to try. Assuming the invisible data is in A1, what does =LEN(A1) return? If you don't get a 0, what does =CODE(LEFT(A1),1) give you?
LEN function gives 0
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Well, can't explain it but now you have your formula to identify them:

=(LEN(A1)=0)*(1-ISBLANK(A1))
 

Trevarrick

New Member
Joined
Sep 1, 2009
Messages
10

ADVERTISEMENT

If it helps, the COUNTA function also sees the offending 'blank' cells and counts them...

Thanks for your responses so far!
 

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,350
Based on your result from CODE and LEN I'd have to say the cell is empty.

What format is the original data in?
Could it have been created with some other version of Excel?

Could you post the file? Or a section of it?
 

Trevarrick

New Member
Joined
Sep 1, 2009
Messages
10
Great, Richard - That does identify the b*****s and enables me to solve my problem.

Does anyone know a possible cause for this annoyance?
 

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