Vlookup data discrepancy

How do I remove the hidden apostrophy in a cell (see e.g.)? I have tried
Find/Replace, changed the Alignment and formatted the cell but failed.

e.g.

'alphanumericdata

EK,

That apostrophe means that the cell contains text. Why do you care that it's
there? In general, you can just leave it. It' won't show up in the cell,
won't print.
--
Earl Kiosterud
www.smokeylake.com

"EK" <[email protected]> wrote in message
news:[email protected]...
> How do I remove the hidden apostrophy in a cell (see e.g.)? I have tried
> Find/Replace, changed the Alignment and formatted the cell but failed.
>
> e.g.
>
> 'alphanumericdata

I am referencing the cells (200 rows) during vlookup and the cells have
random mix of formats, i.e., some are with apostrophe and some without. This
caused a #N/A even though the data are there. I would like to cleanup the
cells so all the data do not have the apostrophe.

"Earl Kiosterud" wrote:

> EK,
>
> That apostrophe means that the cell contains text. Why do you care that it's
> there? In general, you can just leave it. It' won't show up in the cell,
> won't print.
> --
> Earl Kiosterud
> www.smokeylake.com
>
> "EK" <[email protected]> wrote in message
> news:[email protected]...
> > How do I remove the hidden apostrophy in a cell (see e.g.)? I have tried
> > Find/Replace, changed the Alignment and formatted the cell but failed.
> >
> > e.g.
> >
> > 'alphanumericdata
>
>
>

Not sure if this is why you want to remove the apostrophe, but if your
hidden apostrophe is in front of a zero and you need to display the
number with leading zeros but no apostrophes : To get 0123456 from
'123456 :

Column A Column B
'123456 =RIGHT("0000000"&A12,7)

I apologise. Let me try to illustrate:

File A data VLOOKUP value --> File B data RESULT
'abc123 abc123 #N/A

I would like to remove the apostrophe in the data in File A.

Thanks.

"[email protected]" wrote:

> Not sure if this is why you want to remove the apostrophe, but if your
> hidden apostrophe is in front of a zero and you need to display the
> number with leading zeros but no apostrophes : To get 0123456 from
> '123456 :
>
> Column A Column B
> '123456 =RIGHT("0000000"&A12,7)
>
>