with Code in C1:C16 and Calendar year in D1:D16

=VLOOKUP(MID(A1,7,1),$C$1:$D$16,2,)

or

=OFFSET($C$1,MATCH(MID(A1,7,1),$A$1:$A$16-1,1)

adjust ranges to suit

HIH


On 1 Cze, 10:46, 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