I am importing Excel data that has what seems to be blank cells but on closer
inspection I can see an apostrphe in the formula bar. That's not a problem
because I have written a macro to identify apostrophes and clear the contents
of these cells.
The mystery is that when the same data is imported on a friend's computer
with the same version of Excel the apostrophe does not appear in his formula
bar (although they are still non-blank cells) and so my macro will not work
on his computer since it doesn't see the apostrophes. Although I can write a
different macro in his case, I am interested to know what setting in Excel is
responsible for showing apostrophes in blank cells on some computers but not
others.

Thank you

If you have a formula that evaluates to "" (like =if(a1>0,"error","")) and it's
converted to values then you can see this apostrophe--but only if you have a
setting toggled on.

Tools|options|transition tab|check Transition navigation keys.

My macro that clears those entries consists of these manual steps:

edit|replace
what: (leave blank)
with: $$$$$ (my unique string)
replace all

then one more time
edit|replace
what: $$$$$
with: (leave blank)
replace all



Ben wrote:
>
> I am importing Excel data that has what seems to be blank cells but on closer
> inspection I can see an apostrphe in the formula bar. That's not a problem
> because I have written a macro to identify apostrophes and clear the contents
> of these cells.
> The mystery is that when the same data is imported on a friend's computer
> with the same version of Excel the apostrophe does not appear in his formula
> bar (although they are still non-blank cells) and so my macro will not work
> on his computer since it doesn't see the apostrophes. Although I can write a
> different macro in his case, I am interested to know what setting in Excel is
> responsible for showing apostrophes in blank cells on some computers but not
> others.
>
> Thank you

--

Dave Peterson