I need help with solving the following problem. I have develop a spreadsheet
to print "Buyers Guide". (This is the warranty statement on a used car) I
need to write a code or use a function that allowS me to take the model year
from the VIN and populate another cell. The 10th position in the VIN
reprsents the model year; so a 2 in the postion is for a 2002 model year car.
For those years prior to 2000 letters are used to indicate model year (X=
1999).
Any help or suggestions is appreciated.

Walter

Something like this maybe?

=IF(MID(A6,10,1)="X","1999 or earlier",2000+MID(A6,10,1))

I wasn't sure of how the letters relate to the years prior to 2000 (sorry, I
am not familiar with VIN numbers, so all I have to go on is what you post),
so I just lumped them together as "1999 or earlier". If you can provide more
detail about this, we can adjust the formula accordingly.

Rick


"Walter Haenn" <Walter wrote in message
...
I need help with solving the following problem. I have develop a
spreadsheet
to print "Buyers Guide". (This is the warranty statement on a used car)
I
need to write a code or use a function that allowS me to take the model
year
from the VIN and populate another cell. The 10th position in the VIN
reprsents the model year; so a 2 in the postion is for a 2002 model year
car.
For those years prior to 2000 letters are used to indicate model year (X=
1999).
Any help or suggestions is appreciated.

Walter

Rick,
Using MID(A6,10,1) will indentify the 10 digit. I now need to take that
10th digit and translate it to a year. i.e. 4 = 2004, 1 =2001, X= 1999, etc.

"Rick Rothstein (MVP - VB)" wrote:

Something like this maybe?

=IF(MID(A6,10,1)="X","1999 or earlier",2000+MID(A6,10,1))

I wasn't sure of how the letters relate to the years prior to 2000 (sorry, I
am not familiar with VIN numbers, so all I have to go on is what you post),
so I just lumped them together as "1999 or earlier". If you can provide more
detail about this, we can adjust the formula accordingly.

Rick


"Walter Haenn" <Walter wrote in message
...
I need help with solving the following problem. I have develop a
spreadsheet
to print "Buyers Guide". (This is the warranty statement on a used car)
I
need to write a code or use a function that allowS me to take the model
year
from the VIN and populate another cell. The 10th position in the VIN
reprsents the model year; so a 2 in the postion is for a 2002 model year
car.
For those years prior to 2000 letters are used to indicate model year (X=
1999).
Any help or suggestions is appreciated.

Walter

You didn't read my first post carefully enough. The formula I posted will
give you the year for a 10 character that is a number (try it); however, I
said I don't know how VIN numbers work, so I don't know what letters are
possible for the 10th characters besides the "X" you mentioned. Is that the
only possible letter there can be? If so, the formula I posted will work.
However, if there can be other letters, you need to tells us what they are
and what years they represent. I might have guessed that "W" was 1998 and
"V" was 1997, but that would mean VIN number system was created in something
like 1977 which seemed unlikely to me.

Rick


"Walter Haenn" wrote in message
...
Rick,
Using MID(A6,10,1) will indentify the 10 digit. I now need to take that
10th digit and translate it to a year. i.e. 4 = 2004, 1 =2001, X= 1999,
etc.

"Rick Rothstein (MVP - VB)" wrote:

Something like this maybe?

=IF(MID(A6,10,1)="X","1999 or earlier",2000+MID(A6,10,1))

I wasn't sure of how the letters relate to the years prior to 2000
(sorry, I
am not familiar with VIN numbers, so all I have to go on is what you
post),
so I just lumped them together as "1999 or earlier". If you can provide
more
detail about this, we can adjust the formula accordingly.

Rick


"Walter Haenn" <Walter wrote in message
...
I need help with solving the following problem. I have develop a
spreadsheet
to print "Buyers Guide". (This is the warranty statement on a used
car)
I
need to write a code or use a function that allowS me to take the model
year
from the VIN and populate another cell. The 10th position in the VIN
reprsents the model year; so a 2 in the postion is for a 2002 model
year
car.
For those years prior to 2000 letters are used to indicate model year
(X=
1999).
Any help or suggestions is appreciated.

Walter

Rick,
I appreciate your help. I am not sure that I am explaning myself correctly.
I tested the function MID in my current worksheet, and it gave me the
correct digit for the 10th position in the VIN. I now need to take the that
digit anKd translate into a year. The reason is that the VIN and the
modelyear in the VIN will change with every entry. We currently are using
model year 1996 to 2007 in our program. While you were answewring this
posted, I was testing the LOOKUP function, but have run into a snag. The
formula will not accept and entry from another cell that is dependent on
another cell.
Here is what I am using... =LOOKUP(O3,K6:K16,L6:L16) where O3 has the
result of the MID function you help me with. K6:K16 is the range of digits
from the MID range ( X to 7) L6:L16 are the Model Years that correspond.

When I try this I get "#N/A"

Thank you for your help.

Walter

"Rick Rothstein (MVP - VB)" wrote:

You didn't read my first post carefully enough. The formula I posted will
give you the year for a 10 character that is a number (try it); however, I
said I don't know how VIN numbers work, so I don't know what letters are
possible for the 10th characters besides the "X" you mentioned. Is that the
only possible letter there can be? If so, the formula I posted will work.
However, if there can be other letters, you need to tells us what they are
and what years they represent. I might have guessed that "W" was 1998 and
"V" was 1997, but that would mean VIN number system was created in something
like 1977 which seemed unlikely to me.

Rick


"Walter Haenn" wrote in message
...
Rick,
Using MID(A6,10,1) will indentify the 10 digit. I now need to take that
10th digit and translate it to a year. i.e. 4 = 2004, 1 =2001, X= 1999,
etc.

"Rick Rothstein (MVP - VB)" wrote:

Something like this maybe?

=IF(MID(A6,10,1)="X","1999 or earlier",2000+MID(A6,10,1))

I wasn't sure of how the letters relate to the years prior to 2000
(sorry, I
am not familiar with VIN numbers, so all I have to go on is what you
post),
so I just lumped them together as "1999 or earlier". If you can provide
more
detail about this, we can adjust the formula accordingly.

Rick


"Walter Haenn" <Walter wrote in message
...
I need help with solving the following problem. I have develop a
spreadsheet
to print "Buyers Guide". (This is the warranty statement on a used
car)
I
need to write a code or use a function that allowS me to take the model
year
from the VIN and populate another cell. The 10th position in the VIN
reprsents the model year; so a 2 in the postion is for a 2002 model
year
car.
For those years prior to 2000 letters are used to indicate model year
(X=
1999).
Any help or suggestions is appreciated.

Walter

Ok, Rick I guess this is why I need your help. I am a little thick. I try
the formula as you strongly suggested, and it works. However, I need to
modify it for model years 1999 and earlier. Thoughts?

Thank you

Walter Haenn

"Walter Haenn" wrote:

Rick,
I appreciate your help. I am not sure that I am explaning myself correctly.
I tested the function MID in my current worksheet, and it gave me the
correct digit for the 10th position in the VIN. I now need to take the that
digit anKd translate into a year. The reason is that the VIN and the
modelyear in the VIN will change with every entry. We currently are using
model year 1996 to 2007 in our program. While you were answewring this
posted, I was testing the LOOKUP function, but have run into a snag. The
formula will not accept and entry from another cell that is dependent on
another cell.
Here is what I am using... =LOOKUP(O3,K6:K16,L6:L16) where O3 has the
result of the MID function you help me with. K6:K16 is the range of digits
from the MID range ( X to 7) L6:L16 are the Model Years that correspond.

When I try this I get "#N/A"

Thank you for your help.

Walter

"Rick Rothstein (MVP - VB)" wrote:

You didn't read my first post carefully enough. The formula I posted will
give you the year for a 10 character that is a number (try it); however, I
said I don't know how VIN numbers work, so I don't know what letters are
possible for the 10th characters besides the "X" you mentioned. Is that the
only possible letter there can be? If so, the formula I posted will work.
However, if there can be other letters, you need to tells us what they are
and what years they represent. I might have guessed that "W" was 1998 and
"V" was 1997, but that would mean VIN number system was created in something
like 1977 which seemed unlikely to me.

Rick


"Walter Haenn" wrote in message
...
Rick,
Using MID(A6,10,1) will indentify the 10 digit. I now need to take that
10th digit and translate it to a year. i.e. 4 = 2004, 1 =2001, X= 1999,
etc.

"Rick Rothstein (MVP - VB)" wrote:

Something like this maybe?

=IF(MID(A6,10,1)="X","1999 or earlier",2000+MID(A6,10,1))

I wasn't sure of how the letters relate to the years prior to 2000
(sorry, I
am not familiar with VIN numbers, so all I have to go on is what you
post),
so I just lumped them together as "1999 or earlier". If you can provide
more
detail about this, we can adjust the formula accordingly.

Rick


"Walter Haenn" <Walter wrote in message
...
I need help with solving the following problem. I have develop a
spreadsheet
to print "Buyers Guide". (This is the warranty statement on a used
car)
I
need to write a code or use a function that allowS me to take the model
year
from the VIN and populate another cell. The 10th position in the VIN
reprsents the model year; so a 2 in the postion is for a 2002 model
year
car.
For those years prior to 2000 letters are used to indicate model year
(X=
1999).
Any help or suggestions is appreciated.

Walter