I need to search for then extract a specific portion of cell data.
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
__________________________
|