On Tue, 05 Sep 2006 22:35:15 GMT, "Ken" wrote:



Column "A" (raw data) 1st Formula Results
2nd
Formula Results
8675-US (59) 59
1 (should be 59)
8675-EU x 5 #VALUE!
5
6330-02(18) 18
1 (should be 18)
6330-02CE (x10 ) #VALUE!
1
(should be 10)
6305-02 (1) 1
1
58516 (x117) #VALUE!
1 (should be 117)
6312-05(X30) #VALUE!
1 (should be 30)
604-800006-002 #VALUE!
1
5054-SUA-US #VALUE!
1
6312-05(X20 ) #VALUE!
1 (should be 20)
6420-05(30) 30
1 (should be 30)
6420-05 ( 9 ) 9
1 (should be 9)

I really appreciate your help!

Thanks,
//Ken

"Toppers" wrote in message
...
Sorry but it DOES capture (X4), (X10), (X117). These are a direct
copy
of
my results using the supplied formula.

700001103 (x4) 4
8675-US (59) 59
8675-EU x 5 5
6330-02(18) 18
6330-02CE (x10 ) 10
6305-02 (1) 1
58516 (x117) 117

It doesn't capture the situation where no quantity was present but
this
will:

=IF(ISERROR(VALUE(TRIM(MID(SUBSTITUTE(SUBSTITUTE(S UBSTITUTE(A8,"(","
"),"x"," "),")",""),FIND(" ",SUBSTITUTE(SUBSTITUTE(A8,"("," "),"x","

")),255)))),1,VALUE(TRIM(MID(SUBSTITUTE(SUBSTITUT E(SUBSTITUTE(A8,"(","
"),"x"," "),")",""),FIND(" ",SUBSTITUTE(SUBSTITUTE(A8,"("," "),"x","
")),255))))

700001103 (x4) 4
8675-US (59) 59
8675-EU x 5 5
6330-02(18) 18
6330-02CE (x10 ) 10
6305-02 (1) 1
58516 (x117) 117
58517 1

HTH

"Ken" wrote:

Richard,

I think your on to something here. Toppers response worked great
except,
as
you pointed out, it only captures the numbers in (##) or ( ## ).
It does
not
capture numbers in (x##) or ( x## ) or (X##), etc... Ultimately, I
need
to
capture all scenarios. If the part number does not have a quantity
at
the
end (usually indicated by the "x/X" or "(" ), then the result
should be
the
number "1", indicating a single part number.

EXAMPLE:
Cell "A"
700001103 (x4)
(part number) 700001103 (quantity of) (x4) < (i need
just the
number indicating the quantity, regardless of what it is/is not
wrapped
in)
4

Is this possible to do?

Thanks again, to you and Toppers
//Ken

"Richard Buttrey" wrote
in
message ...
What do you mean by foolproof?

It is foolproof for the limited set of 6 records you give as an
example.

Have you any other formats that are not covered by
the rules covered.

i.e
include characters between "(" and ")" unless the character is
"x" or
" " (space)
or if null result include all characters following the last
space

Regards

On Fri, 1 Sep 2006 11:27:02 -0700, Toppers
wrote:

Ken,
I tested this in your sample and it appears to work
BUT I
cannot
guarantee it is "foolproof"!

=VALUE(TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( A1,"(","
"),"x","
"),")",""),FIND(" ",SUBSTITUTE(SUBSTITUTE(A1,"("," "),"x","
")),255)))

HTH

"Ken" wrote:

I need to search for then extract a specific portion of cell
data.
Below, I
have provided an example of what I'm trying to do.

Column "A" (raw data) Column "B" (end result)
700001103 (x4) 4
8675-US (59) 59
8675-EU x 5 5
6330-02(18) 18
6330-02CE (x10 ) 10
6305-02 (1) 1
58516 (x117) 117



It appears to me that what you want to do is capture the last numeric value in
the string. If the rule is different, see if you can write it out.

The following will give the results you have specified in your examples, using
that rule.

First, download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr

Then, with your string in A1, use the formula:

=REGEX.MID(A1,"\d+",-1)

This will return the value as TEXT. If you want the value as a NUMBER, precede
the formula with a double unary:

=--REGEX.MID(A1,"\d+",-1)

--ron