Apostrophe

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

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.

--
Regards,
Tom Ogilvy


"Ben" <[email protected]> wrote in message
news:[email protected]...
> 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

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

--
HTH

Bob Phillips

"Ben" <[email protected]> wrote in message
news:[email protected]...
> 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

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

--
Regards,
Tom Ogilvy


"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> Check Tools>Options>Transition, and see if the Transition navigation keys
> box is checked, if so, uncheck it.
>
> --
> HTH
>
> Bob Phillips
>
> "Ben" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>

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.

--
HTH

Bob Phillips

"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> Check Tools>Options>Transition, and see if the Transition navigation keys
> box is checked, if so, uncheck it.
>
> --
> HTH
>
> Bob Phillips
>
> "Ben" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>

One day, I will install 2003 <G>

Bob

"Tom Ogilvy" <[email protected]> wrote in message
news:[email protected]...
> Just for info, that didn't affect the visibility of a leading apostrophe
in
> Excel 2003.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Bob Phillips" <[email protected]> wrote in message
> news:[email protected]...
> > Check Tools>Options>Transition, and see if the Transition navigation
keys
> > box is checked, if so, uncheck it.
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > "Ben" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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 I had a formula that evaluated to "", and then converted to values, this
setting showed that apostrophe.

(also xl2003)

Tom Ogilvy wrote:
>
> Just for info, that didn't affect the visibility of a leading apostrophe in
> Excel 2003.
>
> --
> Regards,
> Tom Ogilvy
>
> "Bob Phillips" <[email protected]> wrote in message
> news:[email protected]...
> > Check Tools>Options>Transition, and see if the Transition navigation keys
> > box is checked, if so, uncheck it.
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > "Ben" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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

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

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

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

--
Regards,
Tom Ogilvy

"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
> If I had a formula that evaluated to "", and then converted to values,
this
> setting showed that apostrophe.
>
> (also xl2003)
>
> Tom Ogilvy wrote:
> >
> > Just for info, that didn't affect the visibility of a leading apostrophe
in
> > Excel 2003.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "Bob Phillips" <[email protected]> wrote in message
> > news:[email protected]...
> > > Check Tools>Options>Transition, and see if the Transition navigation
keys
> > > box is checked, if so, uncheck it.
> > >
> > > --
> > > HTH
> > >
> > > Bob Phillips
> > >
> > > "Ben" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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