Vlookup exclude first character

[rkr786]

Hi all

i HAVE 2 SPREADSHEETS.

1 IS: EH_Cr main where in cell I16 I want a vlookup formula that checks if the barcode in E16 is present in the other spreadsheet EH_SRM1155501.202.CSV, column I. The only issue is, column I has an apostrophe in front of the numbers. How can I write a vlookup substitute formula to say exlcude the apostrophe/first character?

Thank you!

EH_SRM1155501.202.csv
	A
5	'20171605
EH_SRM1155501.202

 

[rkr786]

Adding my main sheet...

EH_Cr main.xlsx
	I
6
NPD

 

[rkr786]

SORRY Not sure what happened above here are images of both files.....

 

[FormR]

How can I write a vlookup substitute formula to say exlcude the apostrophe/first character?

How about instead, adding the apostrophe to the lookup value.

i.e. =VLOOKUP("'"&E16,...and the rest of your formula...)

 

[rkr786]

How about instead, adding the apostrophe to the lookup value.

i.e. =VLOOKUP("'"&E16,...and the rest of your formula...)

hI FormR I dont think that worked: =VLOOKUP("'"&[EH_SRM1155501.202.csv]SRM1155501.202!$I$16,E:E,1,FALSE)

 

[FormR]

Shouldn't it be?

=VLOOKUP("'"&E16,[EH_SRM1155501.202.csv]SRM1155501.202!$I$I,1,FALSE)

 

[rkr786]

Shouldn't it be?

=VLOOKUP("'"&E16,[EH_SRM1155501.202.csv]SRM1155501.202!$I$I,1,FALSE)

YES, SOLVED, THANK YOU!