Apostrophe

[Ben]

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

 

[Tom Ogilvy]

A leading apostrophe is treated as a formatting character. To the best of
my knowledge, there is no setting that would cause it not to be displayed in
the formula bar if it exists.

Perhaps you need to look at the data source and see if it contains such
apostrophies.

 

[Bob Phillips]

Check Tools>Options>Transition, and see if the Transition navigation keys
box is checked, if so, uncheck it.

 

[Tom Ogilvy]

Just for info, that didn't affect the visibility of a leading apostrophe in
Excel 2003.

 

[Bob Phillips]

BTW, your macro should be flexible to cater for both situations. Check for a
leading apostrophe, and only clear it if there. No different macro required.

 

[Bob Phillips]

One day, I will install 2003 <G>

Bob

Just for info, that didn't affect the visibility of a leading apostrophe in
Excel 2003.

 

[Dave Peterson]

If I had a formula that evaluated to "", and then converted to values, this
setting showed that apostrophe.

(also xl2003)

 

[Dave Peterson]

You've got another reply to your post in .excel

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

 

[Tom Ogilvy]

Maybe that is what Bob meant -- then I withdraw my comment as I actually
entered a single quote in the cell.