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?

 

[Richard Schollar]

Has this data been copied from another source/system outside of Excel? I've seen this from time to time where I've copied/pasted from somewhere else. Can't actually explain why it occurs though

 

[Special-K99]

Null strings?
http://www.ozgrid.com/forum/showthread.php?t=53445

 

[Trevarrick]

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?

Thanks, Special-K99. I don't have the original data, just the Excel file as given to me. I'm not sure how to post the data but I will when I can!

 

[Trevarrick]

Is calculation set to manual?

Calculation is set to automatic, thanks, with zeros set to display

 

[Trevarrick]

Null strings?
http://www.ozgrid.com/forum/showthread.php?t=53445

Seti, I checked your Ozgrid link and I think you're right. I think it's a null string, perhaps from Access. When I use the Excel 'Type' function on the 'blank' cell it returns '2' - a text string. This seems the most likely explanation. You'd think Excel would be able to flag up these problematic cells in some way, especially when they're probably coming from another Microsoft application!

Thanks for your help!

 

[Trevarrick]

Seti, I checked your Ozgrid link and I think you're right. I think it's a null string, perhaps from Access. When I use the Excel 'Type' function on the 'blank' cell it returns '2' - a text string. This seems the most likely explanation. You'd think Excel would be able to flag up these problematic cells in some way, especially when they're probably coming from another Microsoft application!

Thanks for your help!

Correction,

Thanks to Special-K99 for the Ozgrid link re. null strings from Access.

 

[Special-K99]

Originally Posted by Special-K99
Null strings?
http://www.ozgrid.com/forum/showthread.php?t=53445

Seti, I checked your Ozgrid link and I think you're right.

Sniff, sniff. My name has changed to Seti



Glad you got it sorted

 

[Trevarrick]

Apologies for the inadvertant name change, Special-K99, and thanks for yours and everyone else's help.