LOOKUP question

[Dr. Andre Nolen]

Hi,
I'm currently putting together a spreadsheet of customer information for a
mechanic's shop..
Each car that comes in for a service has a unique 17 digit Vehicle
Identification Number, (VIN), which is entered into the spreadsheet.
Each 'digit' of the VIN relates to a significant property of the car such as
'year of manufacture', 'place of manufacture', 'engine size', number of
doors', etc..
What I'm trying to do is break the VIN down into single digits, (possibly
using the MID function), then have a lookup table for each digit so that
ultimately a description of the car is returned...

eg. the VIN = SALLPAMV1GA123456 describes a 4 door Range Rover with a 3.9l
V8 petrol engine, 100" wheelbase, 4 speed manual, Righthand Drive built in
Solihull England in 1990.. etc, etc....

Thanks
Darren

 

[Paul]

Hi,
I'm currently putting together a spreadsheet of customer information for a
mechanic's shop..
Each car that comes in for a service has a unique 17 digit Vehicle
Identification Number, (VIN), which is entered into the spreadsheet.
Each 'digit' of the VIN relates to a significant property of the car such as
'year of manufacture', 'place of manufacture', 'engine size', number of
doors', etc..
What I'm trying to do is break the VIN down into single digits, (possibly
using the MID function), then have a lookup table for each digit so that
ultimately a description of the car is returned...

eg. the VIN = SALLPAMV1GA123456 describes a 4 door Range Rover with a 3.9l
V8 petrol engine, 100" wheelbase, 4 speed manual, Righthand Drive built in
Solihull England in 1990.. etc, etc....

Thanks
Darren

Fine. So what's the question?

 

[Bernard Liengme]

Hi Darren,
Not sure what the question is.
If the VIN numbers are in column A this formula in B1 will give the first
character (they are not digits) =MID($A1,COLUMN()-1,1). If you copy this
across to column R you will get all the character. For the real digits you
may wish to use =VALUE(MID($A1,COLUMN()-1,1))
Copy these down the rows for other VINs
Note that I have assumed the first character is extracted in column B. If it
is in say D, then use =MID($A1,COLUMN()-3,1)
Do you need help with lookup?
Bernard

 

[DB]

Darren,
I just did a project like this. I'll email it to your
address. Hope it helps.
DB

 

[L. Howard Kittle]

Hi Darren,

I would like to see a version of that project, if you please.

(e-mail address removed)

Thanks,
Regards,
Howard