lookup specific character place

[Guest]

I need to Identify the 10th character of F2 and return the correct year in
cell B2.
For example: F2 VIN# JM1HD4616N0112078 - THE "N" identifies the model year
1992.
I need to recognize the10th digit and place "1992" in the "year" field.

I had trouble with wildcards in the "IF" function, any help would be great!

A B C D E F
1 Stock # Year Description Color Miles VIN #
2 HT58904B MAZDA 929 BLK 36725 JM1HD4616N0112078

 

[Domenic]

Let H1:I5 contain your table, something like this...

N 1992
O 1993
P 1994
Q 1995
R 1996

Then use the following formula...

=VLOOKUP(MID(F2,10,1),$H$1:$I$5,2,0)

Adjust the table and the reference for that table accordingly.

Hope this helps!

 

[RagDyeR]

Does the "N" = 1992 follow the alphabet?
O = 1993
P = 1994
Q = 1995

That puts "Z" ending at 2004.
What comes next?

*WITHOUT* making a data list of relations between letters and years, this
will return what you originally asked for:

In B2 enter:

=CODE(MID(F2,10,1))+1914

BUT only works out to 2004!
Plus, it's *not* a real date to XL.

So, post back with your list of letter to year references, and whether or
not you need "real" dates.
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------


I need to Identify the 10th character of F2 and return the correct year in
cell B2.
For example: F2 VIN# JM1HD4616N0112078 - THE "N" identifies the model year
1992.
I need to recognize the10th digit and place "1992" in the "year" field.

I had trouble with wildcards in the "IF" function, any help would be great!

A B C D E F
1 Stock # Year Description Color Miles VIN #
2 HT58904B MAZDA 929 BLK 36725 JM1HD4616N0112078

 

[L. Howard Kittle]

You might try a VLOOKUP.

=VLOOKUP(MID(F2,10,1),M1:N14,2,0)

Where F2 has the VIN # and M1 through M14 has a list of the letters and N1
through N14 has the years that correspond to each letter.

HTH
Regards,
Howard

 

[Guest]

Thanx for your responses, I made a table, altered the formula and I tried the
vlookup and it does not seem to return a result. All motor vehicle id
numbers have 17 digits regardless of make, model, etc...
every id number has a "Year digit" (10th from the left).

L = 1990
M = 1991
N = 1992
P = 1993
R = 1994

Letters that closely reseble numbers are omitted from the code.
after "Y" (2000), numbers identify the year.

1 = 2001
2 = 2002 etc...

the year number I need returned in cell B2 is not in "date" format it is
just a general number.
Just so you know where I am going with this.. Thanx again

 

[Guest]

Got it to work, had 1 cell # wrong,
Thanx again!

 

[Domenic]

Try formatting the first column in your table as 'Text' and re-enter
your values.

Hope this helps!