Vlookup data discrepancy

E

EK

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
 
E

Earl Kiosterud

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.
 
E

EK

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.
 
A

al_mac75

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)
 
E

EK

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.
 
Top