Assuming that your codes/calendar years are in C1:D16, then
=VLOOKUP(MID(A1,7,1),$C$1:$D$16,2,FALSE)

Please note: You have to format range C1:C16 as text, otherwise the code
will produce errors in all cases where the year is represented by a number
and not a letter.

Joerg Mochikun

"nginhong" wrote in message
...
Dear expert,

If I have an 13 positions alphanumeric string in cell A1 e.g.
A1B2C3YD4E5F6
Position no. 7 represents is calendar year like table below:-

Code Calendar year
Y 2000
1 2001
2 2002
3 2003
4 2004
5 2005
6 2006
7 2007
8 2008
9 2009
A 2010
B 2011
C 2012
D 2013
E 2014
F 2015

How to extract and display the calendar year in cell B2?
Example:-
Cell A1 = A1B2C3YD4E5F6 then return in cell B2 = 2000
Cell A1 = A1B2C35D4E5F6 then return in cell B2 = 2005
Cell A1 = A1B2C3AD4E5F6 then return in cell B2 = 2010

Meaning that you have to first extract the position no. 7 from the string
then VLOOKUP the table above to return to a calendar year in cell B2.

Thanks for your support!

BR//nginhong