Isblank function erroneous results

[Trevarrick]

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?

 

[Special-K99]

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]

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]

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]

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]

Well, can't explain it but now you have your formula to identify them:

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

 

[Trevarrick]

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

Thanks for your responses so far!

 

[Special-K99]

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?

 

[Seti]

Is calculation set to manual?

 

[Trevarrick]

Great, Richard - That does identify the b*****s and enables me to solve my problem.

Does anyone know a possible cause for this annoyance?