Find a range of values within an array

[WCoaster]

On a take off of Formula Forensic No. 021 – Find the 4th Slash !
I am trying to break a part number out of a text string. so I convert the string to a single charachter array and check the code of each charachter to see if it is number greater than 47 AND less than 58. This would tell that charachter is a number. I can find >47 or <58 easy enough. The trouble is to find >47AND<58 in a simple formula.

Here is a sample string:
21001253 CONN,RECEPTACLE,2 PIN,

This particular string has a space after the part number and it would be easy to just find the first " " . However not all data is clean there is not always a space after the last digit in the part number.

Here is my solution so far:

LEFT(V4,SUM((CODE(MID(SUBSTITUTE(V4," ",CHAR(135)),ROW(INDIRECT("1:"&LEN(V4))),1))<58)+0))

The problem is this formula will pick up other punctuation marks to count the length of the result string and cause an inaccurate result. eg.

21001253 CON

Instead of:

21001253

Because CODE(",") =44 making the Num_Chars in the LEFT func =12 instead of 10. Doh!!! I just realised there is an extra digit in the string. that will also return inaccurate results.

Anyway I have found my own solution unique to my data set (max part number can only be 13 charachters.... Probably. So instead of checking the entire string I will only check the first 13 or 14 charachters) but I would like to see others ideas if you care to try. I would still like to know how to check if each item in an array is between a range. For example: How many charachters in a text string are numbers 0-9?

Thanks
WCoaster

 

[Mike H..]

On a take off of Formula Forensic No. 021 – Find the 4th Slash !
I am trying to break a part number out of a text string. so I convert the string to a single charachter array and check the code of each charachter to see if it is number greater than 47 AND less than 58. This would tell that charachter is a number. I can find >47 or <58 easy enough. The trouble is to find >47AND<58 in a simple formula.

Here is a sample string:
21001253 CONN,RECEPTACLE,2 PIN,

This particular string has a space after the part number and it would be easy to just find the first " " . However not all data is clean there is not always a space after the last digit in the part number. If your part number is somewhere else then a few examples or preferably a workbook.

Here is my solution so far:

LEFT(V4,SUM((CODE(MID(SUBSTITUTE(V4," ",CHAR(135)),ROW(INDIRECT("1:"&LEN(V4))),1))<58)+0))

The problem is this formula will pick up other punctuation marks to count the length of the result string and cause an inaccurate result. eg.

21001253 CON

Instead of:

21001253

Because CODE(",") =44 making the Num_Chars in the LEFT func =12 instead of 10. Doh!!! I just realised there is an extra digit in the string. that will also return inaccurate results.

Anyway I have found my own solution unique to my data set (max part number can only be 13 charachters.... Probably. So instead of checking the entire string I will only check the first 13 or 14 charachters) but I would like to see others ideas if you care to try. I would still like to know how to check if each item in an array is between a range. For example: How many charachters in a text string are numbers 0-9?

Thanks
WCoaster

Hi,

Not wishing to be rude but this question would have been better with a few more examples of your text string replacing the lengthy description. If your part number is always at the front you can use this which doesn't need a space after the part number to work.

=LOOKUP(1E+300,--LEFT(V4,ROW(INDIRECT("1:"&LEN(V4)))))

 

[Hui]

Do the Part No.'s always have 8 numbers ?

 

[Asheesh]

Since I worked on it..so here is another way out

TRIM(LEFT(A1,MIN(IF(ISNUMBER(--MID($A$1,ROW(OFFSET($A$1,,,LEN($A$1))),1)),"",ROW(INDIRECT("1:"&LEN($A$1)))))))

Array enter..

 

[WCoaster]

Do the Part No.'s always have 8 numbers ?

No, Part numbers range from 6 to 13 alpha-numeric charachters. field size is 30 in the database but 13 is the max length.