vlOOKUP right function

Hi guys

I have two files I`m trying to do a vlookup right on./

So my first file (eh_master bc, in column G, ,cell G6, I am trying to do a vlookup which says, look for value E6 and the table is on the other spreadsheet:EH_IS_OUTPUT, table array: e:l, and return the data from column L. I am using the right function because the numbers in column E on the EH_IS_OUTPUT sheet has an apostrophe.

Here is what I have tried: =RIGHT(VLOOKUP(E6,'[EH_IS_OUTPUT.xlsx]DS288715-NPD Xmas Tracker 2021 '!$E:$L,1,FALSE),13)

Please note my numbers may consist of as many as 14 numbers and as less as 6 numbers... how should I amend the formula?

THank you.

Maybe try

=SUBSTITUTE(VLOOKUP("'"&E6,'[EH_IS_OUTPUT.xlsx]DS288715-NPD Xmas Tracker 2021 '!$E$1:$L$9999,1,0),"'",)

great thank you it works!