+ Reply to Thread
Results 1 to 12 of 12

Various length alphanumeric field with and without a dash - need to extract numbers

  1. #1
    Registered User
    Join Date
    12-12-2012
    Location
    US
    MS-Off Ver
    Excel 2007
    Posts
    20

    Various length alphanumeric field with and without a dash - need to extract numbers

    I received some help on this post previously and thought it completely solved the problem; however, I spoke too quickly.

    In various examples of product numbers, I need to isolate a few numbers (could be 2 or 3) in order to determine the width. The product number could be of various lengths with or without a dash.

    Here are some examples of product numbers:

    ABCDEFGHIJK60120-200 The answer here is 120
    ABCD1360 The answer here is 60
    ABCD13160-100 The answer here is 160
    ABCDEFGH13160 The answer here is 160

    The rule is find the non-digit codes in the string (the length varies). From that point onwards, look at the remainder of the string, which is numeric but may contain a hyphen.
    If the string contains a hyphen, ignore the next digit and extract the first 2 or 3 numbers up to the hyphen else ignore the next 2 digits immediately following the alpha string and extract the remaining 2 or 3 numbers up to the end of the string.

    Here is the answer I was given, but it only works if the non-digit code length is 4. I need it to work on various lengths non-digit codes.

    *********************************
    Place your original numbers in column A starting at A1
    Now user Helper columns

    in B1
    =NOT(ISERROR(FIND("-",A1)))*1

    in C1
    =IF(LEN(A1)=0,0,MIN(IF(1*ISNUMBER(1*MID(A1,ROW(INDIRECT("A1:A"&LEN(A1))),1))=0,ROW(INDIRECT("A1:A"&LEN(A1))),LEN(A1)+1)))*(ISNUMBER(A1)=FALSE)+4+(2-B1)
    This is an array formula, use CTRL-SHIFT-ENTER
    Source: http://www.cpearson.com/Excel/stringformulas.aspx
    Position of first non-digit in a string

    in D1
    =IF(B1=0,LEN(A1)-C1,FIND("-",A1))+1

    in E1
    =MID(A1,C1,D1)

    Copy these down the columns

    E1 is the result you want
    ************************************

    Thank you

  2. #2
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Various length alphanumeric field with and without a dash - need to extract numbers

    With
    A1: a string beginning with letters and ending with a 4 or 5 digit number and an optional dash followed by digits
    Examples:
    ABCDEFGHIJK60120-200
    ABCD1360
    ABCD13160-100
    ABCDEFGH13160
    ABCDEFGH13060


    This regular formula returns:
    When there are 5 digits: The last 3 digits before the dash (or end of the string, if no dash)
    When there are 4 or less digits: The last 2 digits before the dash (or end of the string, if no dash)
    Please Login or Register  to view this content.
    or...If you need error checking:
    Please Login or Register  to view this content.
    With the above examples, these are the returned results:
    120
    60
    160
    160
    060

    Is that something you can work with?
    Last edited by Ron Coderre; 12-17-2012 at 11:28 AM.
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  3. #3
    Registered User
    Join Date
    10-02-2012
    Location
    Baku
    MS-Off Ver
    Excel 2010
    Posts
    273

    Re: Various length alphanumeric field with and without a dash - need to extract numbers

    Hi.
    =VALUE(IFERROR(IF(SEARCH("-",A1,1),MID(A1,FIND("-",A1,1)-3,3),""),IF(SUM(ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))*1)<=4,RIGHT(A1,2),RIGHT(A1,3)))) CSE

    if the numbers are 4 digits then extracts last 2 numbers otherwise extracts last 3

    Hope this will help
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    12-12-2012
    Location
    US
    MS-Off Ver
    Excel 2007
    Posts
    20

    Re: Various length alphanumeric field with and without a dash - need to extract numbers

    A slight problem if the product number has a zero as a part of the combination. I tested ABCD0973-100 and the above formula returns 973 and the answer should be 73.

  5. #5
    Registered User
    Join Date
    10-02-2012
    Location
    Baku
    MS-Off Ver
    Excel 2010
    Posts
    273

    Re: Various length alphanumeric field with and without a dash - need to extract numbers

    =if(right(a1,3)*1=100,value(mid(a1,find("-",a1,1)-2,2)),value(iferror(if(search("-",a1,1),mid(a1,find("-",a1,1)-3,3),""),if(sum(isnumber(--mid(a1,row(indirect("1:"&len(a1))),1))*1)<=4,right(a1,2),right(a1,3)))))

    cse

  6. #6
    Registered User
    Join Date
    12-12-2012
    Location
    US
    MS-Off Ver
    Excel 2007
    Posts
    20

    Re: Various length alphanumeric field with and without a dash - need to extract numbers

    Please test ABCD13160-100. The formula is returning 60, where it should return 160. In your previous answer it worked, but now it is not.

    Sorry to be a pest.

  7. #7
    Registered User
    Join Date
    10-02-2012
    Location
    Baku
    MS-Off Ver
    Excel 2010
    Posts
    273

    Re: Various length alphanumeric field with and without a dash - need to extract numbers

    Its because of dot after 100,

  8. #8
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Various length alphanumeric field with and without a dash - need to extract numbers

    is there a reason you are not testing the formulas I posted?
    Both of them are regular formulas and neither of them exhibit the issues you mention.

  9. #9
    Registered User
    Join Date
    12-12-2012
    Location
    US
    MS-Off Ver
    Excel 2007
    Posts
    20

    Re: Various length alphanumeric field with and without a dash - need to extract numbers

    Sorry, I'm just now realizing that the posts were from two different people. Thought it was the same source.

    Ok, I just tested......for the example BSHPFRSUR46020-200, it is returning 020 versus just 20. And it is making the answer all text, whereas they need to be values, so that I can calculate on them. I could just multiple the result by 1 to turn into a value. But, what about the 020 versus the needed 20?

    Thanks

  10. #10
    Registered User
    Join Date
    12-12-2012
    Location
    US
    MS-Off Ver
    Excel 2007
    Posts
    20

    Re: Various length alphanumeric field with and without a dash - need to extract numbers

    Actually, the BSHPFRSUR46020-200 is a typo on my part. After I corrected the product #, the result worked correctly.

    Thank you!!

  11. #11
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Various length alphanumeric field with and without a dash - need to extract numbers

    I'd assumed you might want to see the leading zeros.
    Since you don't, try this slight variation:
    Please Login or Register  to view this content.

  12. #12
    Registered User
    Join Date
    12-12-2012
    Location
    US
    MS-Off Ver
    Excel 2007
    Posts
    20

    Re: Various length alphanumeric field with and without a dash - need to extract numbers

    Wonderful.

    Thank you

+ 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