Left function on VLookup

Hi everyone,

I'm trying to get the Left function working on a vlookup and I'm not having any success. I have the following formula:

=VLOOKUP(LEFT(A3,8)+0,'...Consolidated Listing'!E$3:CG$6472,13,FALSE)

The '...Consolidated Listing is there purposely. I keep getting a #VALUE! error. Any ideas what I'm doing wrong? The value in A3 is alpha-numeric and is generated from its own Right function formula.

Thanks!!

Its both: alpha-numeric.

If you are getting #VALUE it implies that the LEFT(A3,8)+0 is an invalid operation. What do you have in A3, and what does LEFT(A3,8) return?

Pete

The value in A3 is generated by the formula:

=RIGHT(B3,13)

The result is alpha-numeric, i.e. AB123456.xlsx

So i wanted the left function to take just the first 8 characters.

But that will give you AB123456, and if you try to add zero onto that you will get the #VALUE error. What is the purpose of the +0? That is usually used to convert a text string which only contains digits into a proper number - are you really after 123456?

Pete

Crap, got it working. Didn't need the +0 bit. Thanks everyone.