+ Reply to Thread
Results 1 to 6 of 6

labeling Alphanumeric Serial #s from a range..

  1. #1
    Registered User
    Join Date
    09-09-2013
    Location
    Kennesaw, GA, USA
    MS-Off Ver
    Excel 2013
    Posts
    42

    labeling Alphanumeric Serial #s from a range..

    Hello,

    I have a long list of alphanumeric serial numbers and I know what model they are if they fall within specific ranges - see attached.
    Each range is defined by an Alpha Numeric serial range. all the lookup functions I have seen work on numeric ranges but I can't get those to work here..?

    Trying to use something like vlookup, index, match or other combination to label my list from the chart of model ranges.
    Any assistance would be much appreciated.
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    01-07-2013
    Location
    Michigan
    MS-Off Ver
    O365
    Posts
    601

    Re: labeling Alphanumeric Serial #s from a range..

    Are all your serial #s in the format of 3 letters, then a dash, then 7 numbers?
    If someone helped achieve your solution, consider clicking "* Add Reputation" on their post.

    If your question has been answered, mark the thread as [SOLVED] using the Thread Tools menu at the top of the thread.

  3. #3
    Spammer
    Join Date
    01-08-2015
    Location
    Cali, Colombia
    MS-Off Ver
    365
    Posts
    302

    Re: labeling Alphanumeric Serial #s from a range..

    Hi, grasstoe!

    If you have same number of characters in codes (like your example), we could use:
    [B2] : =IFERROR(INDEX(F$2:F$5,MATCH(1,INDEX((A2>=D$2:D$5)*(A2<=E$2:E$5),),)),"")

    If not, you could use:
    [B2] : =IFERROR(INDEX(F$2:F$5,MATCH(1,INDEX((-MID(A2,5,9)<=-SUBSTITUTE(D$2:D$5,LEFT(A2,4),""))*(-MID(A2,5,9)>=-SUBSTITUTE(E$2:E$5,LEFT(A2,4),"")),),)),"")

    And drag it down. Blessings!

  4. #4
    Registered User
    Join Date
    09-09-2013
    Location
    Kennesaw, GA, USA
    MS-Off Ver
    Excel 2013
    Posts
    42

    Re: labeling Alphanumeric Serial #s from a range..

    So close, thank you for such quick responses!

    I think my problem is that there are enough 3 digit codes at the beginning that the purely numeric digits after the dash overlap, but I may be wrong. It totally works on my sample but seems to pick the last value from the list of ranges for all samples on my actual document.

    I will keep struggling and post any solutions that come out...

    Thank YOU!!

  5. #5
    Valued Forum Contributor
    Join Date
    01-07-2013
    Location
    Michigan
    MS-Off Ver
    O365
    Posts
    601

    Re: labeling Alphanumeric Serial #s from a range..

    Try this in B2, entered as an array formula with ctrl+shift+enter:
    Please Login or Register  to view this content.
    It will error out if nothing is found, but that can be handled with an IFERROR formula. Let us know if it works.

  6. #6
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: labeling Alphanumeric Serial #s from a range..

    Another way. The DECIMAL function can convert alpha-numeric strings to numbers with an appropriate base.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Last edited by FlameRetired; 05-23-2019 at 03:24 AM.
    Dave

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Count a range using serial #'s tracked
    By arumble in forum Excel General
    Replies: 7
    Last Post: 03-04-2019, 02:26 PM
  2. [SOLVED] How to list down the Serial No. by date range
    By pakida in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-29-2018, 10:29 PM
  3. Creating alphanumeric serial numbers
    By Walanflower in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 03-26-2018, 03:09 AM
  4. [SOLVED] Number Range Labeling Formula
    By Bloemendaal in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-13-2017, 12:34 PM
  5. Serial number creation / pre-defined alphanumeric sequence / formula
    By beano129 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-08-2012, 02:27 AM
  6. [SOLVED] breaking a sequential serial range
    By shon in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-20-2012, 09:34 PM
  7. Finding a serial number in a list of serial numbers
    By zocoloco in forum Excel General
    Replies: 2
    Last Post: 02-04-2009, 05:20 AM

Tags for this Thread

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