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

1. ## 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. ## 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?

3. ## 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

4. ## 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. ## 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. ## 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. ## Re: Various length alphanumeric field with and without a dash - need to extract numbers

Its because of dot after 100,

8. ## 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. ## 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. ## 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. ## 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. ## Re: Various length alphanumeric field with and without a dash - need to extract numbers

Wonderful.

Thank you

##### Users Browsing this Thread

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

#### 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