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

I hope someone can help me.

Thanks.
//Ken

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(A 1,"("," "),"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

I hope someone can help me.

Thanks.
//Ken

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

I hope someone can help me.

Thanks.
//Ken




__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

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

I hope someone can help me.

Thanks.
//Ken




__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

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(SUBSTITUTE (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

I hope someone can help me.

Thanks.
//Ken




__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

Toppers -

I didn't mean to imply your formula didn't work, just that it didn't work
correctly for me, in my spreadsheet. Below, I have list the results from
your 1st and 2nd formulas, running it on the same list both times. As you
can see, in my spreadsheet, the results aren't what I expected. It seems
where not capturing all the numbers. Any thoughts?

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(SUBSTITUTE (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

I hope someone can help me.

Thanks.
//Ken




__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

Hi,

This seems to work for your set of 6 records. Obviously it will nned
modifying if there are any formats that don't meet the rule I
explained in the last post.

Watch the word wrap, this is all one formula.

=IF(ISERROR(FIND("(",A1)),RIGHT(A1,LEN(A1)-FIND("x",A1)-1),SUBSTITUTE(MID(A1,FIND("(",A1)+1,FIND(")",A1)-FIND("(",A1)-1),"x",""))

Rgds



On Fri, 01 Sep 2006 20:06:14 GMT, "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

I hope someone can help me.

Thanks.
//Ken




__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________


__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

Richard -

Thanks for your help. The formula you provided doesn't seem to work for me.
Although it captures the number, it is also capturing the "x/X". Also, where
there is no number to capture, it's providing a "#VALUE!" instead of a "1".

Thanks again,
//Ken


"Richard Buttrey" wrote in
message ...

Hi,

This seems to work for your set of 6 records. Obviously it will nned
modifying if there are any formats that don't meet the rule I
explained in the last post.

Watch the word wrap, this is all one formula.


=IF(ISERROR(FIND("(",A1)),RIGHT(A1,LEN(A1)-FIND("x",A1)-1),SUBSTITUTE(MID(A1
,FIND("(",A1)+1,FIND(")",A1)-FIND("(",A1)-1),"x",""))

Rgds



On Fri, 01 Sep 2006 20:06:14 GMT, "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

I hope someone can help me.

Thanks.
//Ken




__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________


__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________