+ Reply to Thread
Results 1 to 25 of 25

lookup specific character place

  1. #1
    Domenic
    Guest

    Re: lookup specific character place

    Let H1:I5 contain your table, something like this...

    N 1992
    O 1993
    P 1994
    Q 1995
    R 1996

    Then use the following formula...

    =VLOOKUP(MID(F2,10,1),$H$1:$I$5,2,0)

    Adjust the table and the reference for that table accordingly.

    Hope this helps!

    In article <[email protected]>,
    Budwho <[email protected]> wrote:

    > I need to Identify the 10th character of F2 and return the correct year in
    > cell B2.
    > For example: F2 VIN# JM1HD4616N0112078 - THE "N" identifies the model year
    > 1992.
    > I need to recognize the10th digit and place "1992" in the "year" field.
    >
    > I had trouble with wildcards in the "IF" function, any help would be great!
    >
    > A B C D E F
    > 1 Stock # Year Description Color Miles VIN #
    > 2 HT58904B MAZDA 929 BLK 36725 JM1HD4616N0112078


  2. #2
    RagDyeR
    Guest

    Re: lookup specific character place

    Does the "N" = 1992 follow the alphabet?
    O = 1993
    P = 1994
    Q = 1995

    That puts "Z" ending at 2004.
    What comes next?

    *WITHOUT* making a data list of relations between letters and years, this
    will return what you originally asked for:

    In B2 enter:

    =CODE(MID(F2,10,1))+1914

    BUT only works out to 2004!
    Plus, it's *not* a real date to XL.

    So, post back with your list of letter to year references, and whether or
    not you need "real" dates.
    --

    Regards,

    RD
    --------------------------------------------------------------------
    Please keep all correspondence within the Group, so all may benefit !
    --------------------------------------------------------------------


    "Budwho" <[email protected]> wrote in message
    news:[email protected]...
    I need to Identify the 10th character of F2 and return the correct year in
    cell B2.
    For example: F2 VIN# JM1HD4616N0112078 - THE "N" identifies the model year
    1992.
    I need to recognize the10th digit and place "1992" in the "year" field.

    I had trouble with wildcards in the "IF" function, any help would be great!

    A B C D E F
    1 Stock # Year Description Color Miles VIN #
    2 HT58904B MAZDA 929 BLK 36725 JM1HD4616N0112078



  3. #3
    Domenic
    Guest

    Re: lookup specific character place

    Try formatting the first column in your table as 'Text' and re-enter
    your values.

    Hope this helps!

    In article <[email protected]>,
    Budwho <[email protected]> wrote:

    > Thanx for your responses, I made a table, altered the formula and I tried the
    > vlookup and it does not seem to return a result. All motor vehicle id
    > numbers have 17 digits regardless of make, model, etc...
    > every id number has a "Year digit" (10th from the left).
    >
    > L = 1990
    > M = 1991
    > N = 1992
    > P = 1993
    > R = 1994
    >
    > Letters that closely reseble numbers are omitted from the code.
    > after "Y" (2000), numbers identify the year.
    >
    > 1 = 2001
    > 2 = 2002 etc...
    >
    > the year number I need returned in cell B2 is not in "date" format it is
    > just a general number.
    > Just so you know where I am going with this.. Thanx again
    >
    >
    > "Budwho" wrote:
    >
    > > I need to Identify the 10th character of F2 and return the correct year in
    > > cell B2.
    > > For example: F2 VIN# JM1HD4616N0112078 - THE "N" identifies the model
    > > year
    > > 1992.
    > > I need to recognize the10th digit and place "1992" in the "year" field.
    > >
    > > I had trouble with wildcards in the "IF" function, any help would be great!
    > >
    > > A B C D E F
    > > 1 Stock # Year Description Color Miles VIN #
    > > 2 HT58904B MAZDA 929 BLK 36725 JM1HD4616N0112078


  4. #4
    Budwho
    Guest

    RE: lookup specific character place

    Got it to work, had 1 cell # wrong,
    Thanx again!

    "Budwho" wrote:

    > Thanx for your responses, I made a table, altered the formula and I tried the
    > vlookup and it does not seem to return a result. All motor vehicle id
    > numbers have 17 digits regardless of make, model, etc...
    > every id number has a "Year digit" (10th from the left).
    >
    > L = 1990
    > M = 1991
    > N = 1992
    > P = 1993
    > R = 1994
    >
    > Letters that closely reseble numbers are omitted from the code.
    > after "Y" (2000), numbers identify the year.
    >
    > 1 = 2001
    > 2 = 2002 etc...
    >
    > the year number I need returned in cell B2 is not in "date" format it is
    > just a general number.
    > Just so you know where I am going with this.. Thanx again
    >
    >
    > "Budwho" wrote:
    >
    > > I need to Identify the 10th character of F2 and return the correct year in
    > > cell B2.
    > > For example: F2 VIN# JM1HD4616N0112078 - THE "N" identifies the model year
    > > 1992.
    > > I need to recognize the10th digit and place "1992" in the "year" field.
    > >
    > > I had trouble with wildcards in the "IF" function, any help would be great!
    > >
    > > A B C D E F
    > > 1 Stock # Year Description Color Miles VIN #
    > > 2 HT58904B MAZDA 929 BLK 36725 JM1HD4616N0112078


  5. #5
    L. Howard Kittle
    Guest

    Re: lookup specific character place

    You might try a VLOOKUP.

    =VLOOKUP(MID(F2,10,1),M1:N14,2,0)

    Where F2 has the VIN # and M1 through M14 has a list of the letters and N1
    through N14 has the years that correspond to each letter.

    HTH
    Regards,
    Howard

    "Budwho" <[email protected]> wrote in message
    news:[email protected]...
    >I need to Identify the 10th character of F2 and return the correct year in
    > cell B2.
    > For example: F2 VIN# JM1HD4616N0112078 - THE "N" identifies the model
    > year
    > 1992.
    > I need to recognize the10th digit and place "1992" in the "year" field.
    >
    > I had trouble with wildcards in the "IF" function, any help would be
    > great!
    >
    > A B C D E F
    > 1 Stock # Year Description Color Miles VIN #
    > 2 HT58904B MAZDA 929 BLK 36725 JM1HD4616N0112078




  6. #6
    Budwho
    Guest

    RE: lookup specific character place

    Thanx for your responses, I made a table, altered the formula and I tried the
    vlookup and it does not seem to return a result. All motor vehicle id
    numbers have 17 digits regardless of make, model, etc...
    every id number has a "Year digit" (10th from the left).

    L = 1990
    M = 1991
    N = 1992
    P = 1993
    R = 1994

    Letters that closely reseble numbers are omitted from the code.
    after "Y" (2000), numbers identify the year.

    1 = 2001
    2 = 2002 etc...

    the year number I need returned in cell B2 is not in "date" format it is
    just a general number.
    Just so you know where I am going with this.. Thanx again


    "Budwho" wrote:

    > I need to Identify the 10th character of F2 and return the correct year in
    > cell B2.
    > For example: F2 VIN# JM1HD4616N0112078 - THE "N" identifies the model year
    > 1992.
    > I need to recognize the10th digit and place "1992" in the "year" field.
    >
    > I had trouble with wildcards in the "IF" function, any help would be great!
    >
    > A B C D E F
    > 1 Stock # Year Description Color Miles VIN #
    > 2 HT58904B MAZDA 929 BLK 36725 JM1HD4616N0112078


  7. #7
    Domenic
    Guest

    Re: lookup specific character place

    Try formatting the first column in your table as 'Text' and re-enter
    your values.

    Hope this helps!

    In article <[email protected]>,
    Budwho <[email protected]> wrote:

    > Thanx for your responses, I made a table, altered the formula and I tried the
    > vlookup and it does not seem to return a result. All motor vehicle id
    > numbers have 17 digits regardless of make, model, etc...
    > every id number has a "Year digit" (10th from the left).
    >
    > L = 1990
    > M = 1991
    > N = 1992
    > P = 1993
    > R = 1994
    >
    > Letters that closely reseble numbers are omitted from the code.
    > after "Y" (2000), numbers identify the year.
    >
    > 1 = 2001
    > 2 = 2002 etc...
    >
    > the year number I need returned in cell B2 is not in "date" format it is
    > just a general number.
    > Just so you know where I am going with this.. Thanx again
    >
    >
    > "Budwho" wrote:
    >
    > > I need to Identify the 10th character of F2 and return the correct year in
    > > cell B2.
    > > For example: F2 VIN# JM1HD4616N0112078 - THE "N" identifies the model
    > > year
    > > 1992.
    > > I need to recognize the10th digit and place "1992" in the "year" field.
    > >
    > > I had trouble with wildcards in the "IF" function, any help would be great!
    > >
    > > A B C D E F
    > > 1 Stock # Year Description Color Miles VIN #
    > > 2 HT58904B MAZDA 929 BLK 36725 JM1HD4616N0112078


  8. #8
    Domenic
    Guest

    Re: lookup specific character place

    Let H1:I5 contain your table, something like this...

    N 1992
    O 1993
    P 1994
    Q 1995
    R 1996

    Then use the following formula...

    =VLOOKUP(MID(F2,10,1),$H$1:$I$5,2,0)

    Adjust the table and the reference for that table accordingly.

    Hope this helps!

    In article <[email protected]>,
    Budwho <[email protected]> wrote:

    > I need to Identify the 10th character of F2 and return the correct year in
    > cell B2.
    > For example: F2 VIN# JM1HD4616N0112078 - THE "N" identifies the model year
    > 1992.
    > I need to recognize the10th digit and place "1992" in the "year" field.
    >
    > I had trouble with wildcards in the "IF" function, any help would be great!
    >
    > A B C D E F
    > 1 Stock # Year Description Color Miles VIN #
    > 2 HT58904B MAZDA 929 BLK 36725 JM1HD4616N0112078


  9. #9
    RagDyeR
    Guest

    Re: lookup specific character place

    Does the "N" = 1992 follow the alphabet?
    O = 1993
    P = 1994
    Q = 1995

    That puts "Z" ending at 2004.
    What comes next?

    *WITHOUT* making a data list of relations between letters and years, this
    will return what you originally asked for:

    In B2 enter:

    =CODE(MID(F2,10,1))+1914

    BUT only works out to 2004!
    Plus, it's *not* a real date to XL.

    So, post back with your list of letter to year references, and whether or
    not you need "real" dates.
    --

    Regards,

    RD
    --------------------------------------------------------------------
    Please keep all correspondence within the Group, so all may benefit !
    --------------------------------------------------------------------


    "Budwho" <[email protected]> wrote in message
    news:[email protected]...
    I need to Identify the 10th character of F2 and return the correct year in
    cell B2.
    For example: F2 VIN# JM1HD4616N0112078 - THE "N" identifies the model year
    1992.
    I need to recognize the10th digit and place "1992" in the "year" field.

    I had trouble with wildcards in the "IF" function, any help would be great!

    A B C D E F
    1 Stock # Year Description Color Miles VIN #
    2 HT58904B MAZDA 929 BLK 36725 JM1HD4616N0112078



  10. #10
    L. Howard Kittle
    Guest

    Re: lookup specific character place

    You might try a VLOOKUP.

    =VLOOKUP(MID(F2,10,1),M1:N14,2,0)

    Where F2 has the VIN # and M1 through M14 has a list of the letters and N1
    through N14 has the years that correspond to each letter.

    HTH
    Regards,
    Howard

    "Budwho" <[email protected]> wrote in message
    news:[email protected]...
    >I need to Identify the 10th character of F2 and return the correct year in
    > cell B2.
    > For example: F2 VIN# JM1HD4616N0112078 - THE "N" identifies the model
    > year
    > 1992.
    > I need to recognize the10th digit and place "1992" in the "year" field.
    >
    > I had trouble with wildcards in the "IF" function, any help would be
    > great!
    >
    > A B C D E F
    > 1 Stock # Year Description Color Miles VIN #
    > 2 HT58904B MAZDA 929 BLK 36725 JM1HD4616N0112078




  11. #11
    Budwho
    Guest

    RE: lookup specific character place

    Got it to work, had 1 cell # wrong,
    Thanx again!

    "Budwho" wrote:

    > Thanx for your responses, I made a table, altered the formula and I tried the
    > vlookup and it does not seem to return a result. All motor vehicle id
    > numbers have 17 digits regardless of make, model, etc...
    > every id number has a "Year digit" (10th from the left).
    >
    > L = 1990
    > M = 1991
    > N = 1992
    > P = 1993
    > R = 1994
    >
    > Letters that closely reseble numbers are omitted from the code.
    > after "Y" (2000), numbers identify the year.
    >
    > 1 = 2001
    > 2 = 2002 etc...
    >
    > the year number I need returned in cell B2 is not in "date" format it is
    > just a general number.
    > Just so you know where I am going with this.. Thanx again
    >
    >
    > "Budwho" wrote:
    >
    > > I need to Identify the 10th character of F2 and return the correct year in
    > > cell B2.
    > > For example: F2 VIN# JM1HD4616N0112078 - THE "N" identifies the model year
    > > 1992.
    > > I need to recognize the10th digit and place "1992" in the "year" field.
    > >
    > > I had trouble with wildcards in the "IF" function, any help would be great!
    > >
    > > A B C D E F
    > > 1 Stock # Year Description Color Miles VIN #
    > > 2 HT58904B MAZDA 929 BLK 36725 JM1HD4616N0112078


  12. #12
    Budwho
    Guest

    RE: lookup specific character place

    Thanx for your responses, I made a table, altered the formula and I tried the
    vlookup and it does not seem to return a result. All motor vehicle id
    numbers have 17 digits regardless of make, model, etc...
    every id number has a "Year digit" (10th from the left).

    L = 1990
    M = 1991
    N = 1992
    P = 1993
    R = 1994

    Letters that closely reseble numbers are omitted from the code.
    after "Y" (2000), numbers identify the year.

    1 = 2001
    2 = 2002 etc...

    the year number I need returned in cell B2 is not in "date" format it is
    just a general number.
    Just so you know where I am going with this.. Thanx again


    "Budwho" wrote:

    > I need to Identify the 10th character of F2 and return the correct year in
    > cell B2.
    > For example: F2 VIN# JM1HD4616N0112078 - THE "N" identifies the model year
    > 1992.
    > I need to recognize the10th digit and place "1992" in the "year" field.
    >
    > I had trouble with wildcards in the "IF" function, any help would be great!
    >
    > A B C D E F
    > 1 Stock # Year Description Color Miles VIN #
    > 2 HT58904B MAZDA 929 BLK 36725 JM1HD4616N0112078


  13. #13
    L. Howard Kittle
    Guest

    Re: lookup specific character place

    You might try a VLOOKUP.

    =VLOOKUP(MID(F2,10,1),M1:N14,2,0)

    Where F2 has the VIN # and M1 through M14 has a list of the letters and N1
    through N14 has the years that correspond to each letter.

    HTH
    Regards,
    Howard

    "Budwho" <[email protected]> wrote in message
    news:[email protected]...
    >I need to Identify the 10th character of F2 and return the correct year in
    > cell B2.
    > For example: F2 VIN# JM1HD4616N0112078 - THE "N" identifies the model
    > year
    > 1992.
    > I need to recognize the10th digit and place "1992" in the "year" field.
    >
    > I had trouble with wildcards in the "IF" function, any help would be
    > great!
    >
    > A B C D E F
    > 1 Stock # Year Description Color Miles VIN #
    > 2 HT58904B MAZDA 929 BLK 36725 JM1HD4616N0112078




  14. #14
    Domenic
    Guest

    Re: lookup specific character place

    Try formatting the first column in your table as 'Text' and re-enter
    your values.

    Hope this helps!

    In article <[email protected]>,
    Budwho <[email protected]> wrote:

    > Thanx for your responses, I made a table, altered the formula and I tried the
    > vlookup and it does not seem to return a result. All motor vehicle id
    > numbers have 17 digits regardless of make, model, etc...
    > every id number has a "Year digit" (10th from the left).
    >
    > L = 1990
    > M = 1991
    > N = 1992
    > P = 1993
    > R = 1994
    >
    > Letters that closely reseble numbers are omitted from the code.
    > after "Y" (2000), numbers identify the year.
    >
    > 1 = 2001
    > 2 = 2002 etc...
    >
    > the year number I need returned in cell B2 is not in "date" format it is
    > just a general number.
    > Just so you know where I am going with this.. Thanx again
    >
    >
    > "Budwho" wrote:
    >
    > > I need to Identify the 10th character of F2 and return the correct year in
    > > cell B2.
    > > For example: F2 VIN# JM1HD4616N0112078 - THE "N" identifies the model
    > > year
    > > 1992.
    > > I need to recognize the10th digit and place "1992" in the "year" field.
    > >
    > > I had trouble with wildcards in the "IF" function, any help would be great!
    > >
    > > A B C D E F
    > > 1 Stock # Year Description Color Miles VIN #
    > > 2 HT58904B MAZDA 929 BLK 36725 JM1HD4616N0112078


  15. #15
    Budwho
    Guest

    RE: lookup specific character place

    Got it to work, had 1 cell # wrong,
    Thanx again!

    "Budwho" wrote:

    > Thanx for your responses, I made a table, altered the formula and I tried the
    > vlookup and it does not seem to return a result. All motor vehicle id
    > numbers have 17 digits regardless of make, model, etc...
    > every id number has a "Year digit" (10th from the left).
    >
    > L = 1990
    > M = 1991
    > N = 1992
    > P = 1993
    > R = 1994
    >
    > Letters that closely reseble numbers are omitted from the code.
    > after "Y" (2000), numbers identify the year.
    >
    > 1 = 2001
    > 2 = 2002 etc...
    >
    > the year number I need returned in cell B2 is not in "date" format it is
    > just a general number.
    > Just so you know where I am going with this.. Thanx again
    >
    >
    > "Budwho" wrote:
    >
    > > I need to Identify the 10th character of F2 and return the correct year in
    > > cell B2.
    > > For example: F2 VIN# JM1HD4616N0112078 - THE "N" identifies the model year
    > > 1992.
    > > I need to recognize the10th digit and place "1992" in the "year" field.
    > >
    > > I had trouble with wildcards in the "IF" function, any help would be great!
    > >
    > > A B C D E F
    > > 1 Stock # Year Description Color Miles VIN #
    > > 2 HT58904B MAZDA 929 BLK 36725 JM1HD4616N0112078


  16. #16
    Budwho
    Guest

    RE: lookup specific character place

    Thanx for your responses, I made a table, altered the formula and I tried the
    vlookup and it does not seem to return a result. All motor vehicle id
    numbers have 17 digits regardless of make, model, etc...
    every id number has a "Year digit" (10th from the left).

    L = 1990
    M = 1991
    N = 1992
    P = 1993
    R = 1994

    Letters that closely reseble numbers are omitted from the code.
    after "Y" (2000), numbers identify the year.

    1 = 2001
    2 = 2002 etc...

    the year number I need returned in cell B2 is not in "date" format it is
    just a general number.
    Just so you know where I am going with this.. Thanx again


    "Budwho" wrote:

    > I need to Identify the 10th character of F2 and return the correct year in
    > cell B2.
    > For example: F2 VIN# JM1HD4616N0112078 - THE "N" identifies the model year
    > 1992.
    > I need to recognize the10th digit and place "1992" in the "year" field.
    >
    > I had trouble with wildcards in the "IF" function, any help would be great!
    >
    > A B C D E F
    > 1 Stock # Year Description Color Miles VIN #
    > 2 HT58904B MAZDA 929 BLK 36725 JM1HD4616N0112078


  17. #17
    RagDyeR
    Guest

    Re: lookup specific character place

    Does the "N" = 1992 follow the alphabet?
    O = 1993
    P = 1994
    Q = 1995

    That puts "Z" ending at 2004.
    What comes next?

    *WITHOUT* making a data list of relations between letters and years, this
    will return what you originally asked for:

    In B2 enter:

    =CODE(MID(F2,10,1))+1914

    BUT only works out to 2004!
    Plus, it's *not* a real date to XL.

    So, post back with your list of letter to year references, and whether or
    not you need "real" dates.
    --

    Regards,

    RD
    --------------------------------------------------------------------
    Please keep all correspondence within the Group, so all may benefit !
    --------------------------------------------------------------------


    "Budwho" <[email protected]> wrote in message
    news:[email protected]...
    I need to Identify the 10th character of F2 and return the correct year in
    cell B2.
    For example: F2 VIN# JM1HD4616N0112078 - THE "N" identifies the model year
    1992.
    I need to recognize the10th digit and place "1992" in the "year" field.

    I had trouble with wildcards in the "IF" function, any help would be great!

    A B C D E F
    1 Stock # Year Description Color Miles VIN #
    2 HT58904B MAZDA 929 BLK 36725 JM1HD4616N0112078



  18. #18
    Domenic
    Guest

    Re: lookup specific character place

    Let H1:I5 contain your table, something like this...

    N 1992
    O 1993
    P 1994
    Q 1995
    R 1996

    Then use the following formula...

    =VLOOKUP(MID(F2,10,1),$H$1:$I$5,2,0)

    Adjust the table and the reference for that table accordingly.

    Hope this helps!

    In article <[email protected]>,
    Budwho <[email protected]> wrote:

    > I need to Identify the 10th character of F2 and return the correct year in
    > cell B2.
    > For example: F2 VIN# JM1HD4616N0112078 - THE "N" identifies the model year
    > 1992.
    > I need to recognize the10th digit and place "1992" in the "year" field.
    >
    > I had trouble with wildcards in the "IF" function, any help would be great!
    >
    > A B C D E F
    > 1 Stock # Year Description Color Miles VIN #
    > 2 HT58904B MAZDA 929 BLK 36725 JM1HD4616N0112078


  19. #19
    Budwho
    Guest

    RE: lookup specific character place

    Thanx for your responses, I made a table, altered the formula and I tried the
    vlookup and it does not seem to return a result. All motor vehicle id
    numbers have 17 digits regardless of make, model, etc...
    every id number has a "Year digit" (10th from the left).

    L = 1990
    M = 1991
    N = 1992
    P = 1993
    R = 1994

    Letters that closely reseble numbers are omitted from the code.
    after "Y" (2000), numbers identify the year.

    1 = 2001
    2 = 2002 etc...

    the year number I need returned in cell B2 is not in "date" format it is
    just a general number.
    Just so you know where I am going with this.. Thanx again


    "Budwho" wrote:

    > I need to Identify the 10th character of F2 and return the correct year in
    > cell B2.
    > For example: F2 VIN# JM1HD4616N0112078 - THE "N" identifies the model year
    > 1992.
    > I need to recognize the10th digit and place "1992" in the "year" field.
    >
    > I had trouble with wildcards in the "IF" function, any help would be great!
    >
    > A B C D E F
    > 1 Stock # Year Description Color Miles VIN #
    > 2 HT58904B MAZDA 929 BLK 36725 JM1HD4616N0112078


  20. #20
    Budwho
    Guest

    RE: lookup specific character place

    Got it to work, had 1 cell # wrong,
    Thanx again!

    "Budwho" wrote:

    > Thanx for your responses, I made a table, altered the formula and I tried the
    > vlookup and it does not seem to return a result. All motor vehicle id
    > numbers have 17 digits regardless of make, model, etc...
    > every id number has a "Year digit" (10th from the left).
    >
    > L = 1990
    > M = 1991
    > N = 1992
    > P = 1993
    > R = 1994
    >
    > Letters that closely reseble numbers are omitted from the code.
    > after "Y" (2000), numbers identify the year.
    >
    > 1 = 2001
    > 2 = 2002 etc...
    >
    > the year number I need returned in cell B2 is not in "date" format it is
    > just a general number.
    > Just so you know where I am going with this.. Thanx again
    >
    >
    > "Budwho" wrote:
    >
    > > I need to Identify the 10th character of F2 and return the correct year in
    > > cell B2.
    > > For example: F2 VIN# JM1HD4616N0112078 - THE "N" identifies the model year
    > > 1992.
    > > I need to recognize the10th digit and place "1992" in the "year" field.
    > >
    > > I had trouble with wildcards in the "IF" function, any help would be great!
    > >
    > > A B C D E F
    > > 1 Stock # Year Description Color Miles VIN #
    > > 2 HT58904B MAZDA 929 BLK 36725 JM1HD4616N0112078


  21. #21
    Budwho
    Guest

    lookup specific character place

    I need to Identify the 10th character of F2 and return the correct year in
    cell B2.
    For example: F2 VIN# JM1HD4616N0112078 - THE "N" identifies the model year
    1992.
    I need to recognize the10th digit and place "1992" in the "year" field.

    I had trouble with wildcards in the "IF" function, any help would be great!

    A B C D E F
    1 Stock # Year Description Color Miles VIN #
    2 HT58904B MAZDA 929 BLK 36725 JM1HD4616N0112078

  22. #22
    L. Howard Kittle
    Guest

    Re: lookup specific character place

    You might try a VLOOKUP.

    =VLOOKUP(MID(F2,10,1),M1:N14,2,0)

    Where F2 has the VIN # and M1 through M14 has a list of the letters and N1
    through N14 has the years that correspond to each letter.

    HTH
    Regards,
    Howard

    "Budwho" <[email protected]> wrote in message
    news:[email protected]...
    >I need to Identify the 10th character of F2 and return the correct year in
    > cell B2.
    > For example: F2 VIN# JM1HD4616N0112078 - THE "N" identifies the model
    > year
    > 1992.
    > I need to recognize the10th digit and place "1992" in the "year" field.
    >
    > I had trouble with wildcards in the "IF" function, any help would be
    > great!
    >
    > A B C D E F
    > 1 Stock # Year Description Color Miles VIN #
    > 2 HT58904B MAZDA 929 BLK 36725 JM1HD4616N0112078




  23. #23
    RagDyeR
    Guest

    Re: lookup specific character place

    Does the "N" = 1992 follow the alphabet?
    O = 1993
    P = 1994
    Q = 1995

    That puts "Z" ending at 2004.
    What comes next?

    *WITHOUT* making a data list of relations between letters and years, this
    will return what you originally asked for:

    In B2 enter:

    =CODE(MID(F2,10,1))+1914

    BUT only works out to 2004!
    Plus, it's *not* a real date to XL.

    So, post back with your list of letter to year references, and whether or
    not you need "real" dates.
    --

    Regards,

    RD
    --------------------------------------------------------------------
    Please keep all correspondence within the Group, so all may benefit !
    --------------------------------------------------------------------


    "Budwho" <[email protected]> wrote in message
    news:[email protected]...
    I need to Identify the 10th character of F2 and return the correct year in
    cell B2.
    For example: F2 VIN# JM1HD4616N0112078 - THE "N" identifies the model year
    1992.
    I need to recognize the10th digit and place "1992" in the "year" field.

    I had trouble with wildcards in the "IF" function, any help would be great!

    A B C D E F
    1 Stock # Year Description Color Miles VIN #
    2 HT58904B MAZDA 929 BLK 36725 JM1HD4616N0112078



  24. #24
    Domenic
    Guest

    Re: lookup specific character place

    Try formatting the first column in your table as 'Text' and re-enter
    your values.

    Hope this helps!

    In article <[email protected]>,
    Budwho <[email protected]> wrote:

    > Thanx for your responses, I made a table, altered the formula and I tried the
    > vlookup and it does not seem to return a result. All motor vehicle id
    > numbers have 17 digits regardless of make, model, etc...
    > every id number has a "Year digit" (10th from the left).
    >
    > L = 1990
    > M = 1991
    > N = 1992
    > P = 1993
    > R = 1994
    >
    > Letters that closely reseble numbers are omitted from the code.
    > after "Y" (2000), numbers identify the year.
    >
    > 1 = 2001
    > 2 = 2002 etc...
    >
    > the year number I need returned in cell B2 is not in "date" format it is
    > just a general number.
    > Just so you know where I am going with this.. Thanx again
    >
    >
    > "Budwho" wrote:
    >
    > > I need to Identify the 10th character of F2 and return the correct year in
    > > cell B2.
    > > For example: F2 VIN# JM1HD4616N0112078 - THE "N" identifies the model
    > > year
    > > 1992.
    > > I need to recognize the10th digit and place "1992" in the "year" field.
    > >
    > > I had trouble with wildcards in the "IF" function, any help would be great!
    > >
    > > A B C D E F
    > > 1 Stock # Year Description Color Miles VIN #
    > > 2 HT58904B MAZDA 929 BLK 36725 JM1HD4616N0112078


  25. #25
    Domenic
    Guest

    Re: lookup specific character place

    Let H1:I5 contain your table, something like this...

    N 1992
    O 1993
    P 1994
    Q 1995
    R 1996

    Then use the following formula...

    =VLOOKUP(MID(F2,10,1),$H$1:$I$5,2,0)

    Adjust the table and the reference for that table accordingly.

    Hope this helps!

    In article <[email protected]>,
    Budwho <[email protected]> wrote:

    > I need to Identify the 10th character of F2 and return the correct year in
    > cell B2.
    > For example: F2 VIN# JM1HD4616N0112078 - THE "N" identifies the model year
    > 1992.
    > I need to recognize the10th digit and place "1992" in the "year" field.
    >
    > I had trouble with wildcards in the "IF" function, any help would be great!
    >
    > A B C D E F
    > 1 Stock # Year Description Color Miles VIN #
    > 2 HT58904B MAZDA 929 BLK 36725 JM1HD4616N0112078


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1