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
Bookmarks