You don't really need your lookup table for the coding you posted... with
the exception of the Y, they are Hex values. Try this formula (which does
not reference any table values)...

=2000+HEX2DEC(MID(SUBSTITUTE(A1,"Y","0"),7,1))

Note that in versions of Excel prior to 2007, you need to load the Analysis
ToolPak Add-in (Tools/Add-Ins from Excel's menu bar).

--
Rick (MVP - Excel)


"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