decoding Vin

[jrick47]

Hello,
I'm trying to decode VIN numbers I type into my worksheet. The problem is,
I dont know how to look at the 17 characters and compare it to the
letters/numbers meaning. ex. a Jaguar has the VIN SAJEA51C14WD61012. the
second character denotes the Manuafacturer, in this case A for Jaguar. the
5th, 6th, and 7th determine the model, ie A51 for X-Type. and the 10th
character is the year manufactured. ie 4 for 2004.
How can I pull this information from this number being typed into a cell?
To complicate things further, I have 12 Manufacturers and 22 Models. How
can I type the VIN into one cell, and make one formula look at the
manufacture code and display one of 12 manufacturers, and one formula look at
the model code and display one of 22 models, and one formula display one of 3
years. However, I think the formula for the manufacturer could be eliminated
by looking at the model but I'm not sure how I would do it.
Thank you for any help

 

[Guest]

hi,
Just an idea.
Look up the mid function in help. it will pick out the
various element of the vin number. after that you could
use vlookup to look up that part of the number in a tables
that you would have to create.
may be a lot of work to start but once set up, it should
do what you want.
regards

 

[jrick47]

Thanks man, great idea
However, do you know why it would be giving me a #N/A when I'm looking up
the year. Here's how it looks

Year VIN Year

4 2004
5 2005
6 2006

My formula to find the 10th digit in the Vin is this
=MID(Sheet1!$H$10,10,1) and it works

My formula to insert the year is this
=VLOOKUP(Sheet2!$G$7,Sheet2!$G$3:$H$6,2,FALSE
but it gives me that error, its on another sheet, however it still doesnt
work on the same sheet

Could anyone explain why? Thank you

 

[L. Howard Kittle]

Hi Jrick,

Your MID formula is returning a 4 but it is TEXT and the 4 in your table is
a number.
Try this with your mid formula which converts it from text to a number.

=MID(Sheet1!$H$10,10,1)*1

or you could use the MID function in your VLOOKUP such as:

=VLOOKUP(MID(Sheet1!$H$10,10,1)*1,Sheet2!$G$3:$H$6,2,FALSE)

HTH
Regards,
Howard