Hi,

With your table in columns C&D try this

=IF(ISERROR(MID(A1,7,1)+0),INDEX(D1:D16,MATCH(MID( A1,7,1),C1:C16,0),0),MID(A1,7,1)+2000)

Mike

"nginhong" wrote:

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