| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to extract a character from a string then VLOOKUP a table?
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to extract a character from a string then VLOOKUP a table?
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to extract a character from a string then VLOOKUP a table?
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to extract a character from a string then VLOOKUP a table?
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to extract a character from a string then VLOOKUP a table?
Hi Mike,
The formula is working fine but the cell B2 will shows #N/A when cell A1 is left blank. Could you revise the formula to make cell B2 as "blank" when A1 is left blank? Last time I use this formula to make B2 not to show formula error "#N/A when cell A1 is left blank. =IF(A1=0," ",VLOOKUP(VALUE(MID(A1,7,1)), $C$1:$D$16,2)). BR//nginhong "Mike H" wrote: 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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to extract a character from a string then VLOOKUP a table?
Hi,
=IF(A1<"",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: Hi Mike, The formula is working fine but the cell B2 will shows #N/A when cell A1 is left blank. Could you revise the formula to make cell B2 as "blank" when A1 is left blank? Last time I use this formula to make B2 not to show formula error "#N/A when cell A1 is left blank. =IF(A1=0," ",VLOOKUP(VALUE(MID(A1,7,1)), $C$1:$D$16,2)). BR//nginhong "Mike H" wrote: 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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to extract a character from a string then VLOOKUP a table?
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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to extract a character from a string then VLOOKUP a table?
Here is a formula that does not rely on the Analysis ToolPak and still does
not require your lookup table either... =1999+SEARCH(MID(A1,7,1),"Y123456789ABCDEF") -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Extract one character | Excel Worksheet Functions | |||
| Extract numeric characters plus one character... | Excel Worksheet Functions | |||
| Extract a text string based on character | Excel Worksheet Functions | |||
| Excel-Match 1st text character in a string to a known character? | Excel Worksheet Functions | |||
| extract data up to a certain character | Excel Worksheet Functions | |||
Linear Mode
