"Blank" cells not blank after copying and pasting from Access

I have found similar threads all over the web but that do not discuss my exact problem.
When copying and pasting from Access into Excel, I see blank cells that are not really blank.

=isBlank() returns False, =code() returns #VALUE!, and =char() returns #VALUE!

jQtmhqe.png

When I click into a cell, there is no apparent value, the cursor goes to the far left of the cell. When I highlight the "empty" cells and press delete, the isBlank function returns True.

Therefore, there is something there, but it is not a hidden character as discussed in other threads. It seems to be something else . . .

The problem does not occur when exporting data out of Access and then copy and pasting into Excel. The problem only seems to occur when copying and pasting directly from Access to Excel with null values.

Interestingly, the countif function sees the cells as non-blank but the sumproduct function sees them as blank (presumably because of the number format coercion e.g. sumproduct(--(something),--(something else))

Any ideas on how to prevent this from happening?

Originally Posted by martindwilson

see here
http://blog.contextures.com/archives...from-database/

Those suggestions are all workarounds. So there is no way to have Excel treat null data from an Access copy and paste properly?

The 'empty' cells contain ZLS' (Zero Length String)

Solution is either:

1. use your Access queries to replace "" with NULL.
2. link to the data source from Excel.
3. export the data to Excel.
4. use Excel formulas to sort the ZLS.

Gregg

Originally Posted by GregThePeg

The 'empty' cells contain ZLS' (Zero Length String)

Solution is either:

1. use your Access queries to replace "" with NULL.
2. link to the data source from Excel.
3. export the data to Excel.
4. use Excel formulas to sort the ZLS.

Gregg

Thanks, it all makes sense now. Though it is frustrating that the issue exists when dealing with data across Microsoft products.