I need to find multiply strings within ONE string

Look at DataText To Columns

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Nir" wrote in message
...
I need to find multiply strings within ONE string

Have you considered the SEARCH function?

"Nir" wrote:

I need to find multiply strings within ONE string

I tried both Search and Find but the problem is the I have few options for
the strint to search and these functions don not approve "OR" function.

In other words I want to find "AA" Or "BB" Or "CC" within String "DFAA GTY".
Expected result would be "AA"

"Barb Reinhardt" wrote:

Have you considered the SEARCH function?

"Nir" wrote:

I need to find multiply strings within ONE string

Bob
Please elaborate as i tried that one too.
"Bob Phillips" wrote:

Look at DataText To Columns

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Nir" wrote in message
...
I need to find multiply strings within ONE string

Nir wrote...
I tried both Search and Find but the problem is the I have few options for
the strint to search and these functions don not approve "OR" function.

They do, but it's tricky.

In other words I want to find "AA" Or "BB" Or "CC" within String "DFAA GTY".
Expected result would be "AA"

With "DFAA GTY" in a cell named x,

=MID(x,LOOKUP(1E6,SEARCH({"AA","BB","CC"},x)),2)

would return "AA". If your strings could have multiple instances of any
of these, this particular formula would return the left most instance
of CC first, then the leftmost instance of BB, and finally the leftmost
instance of AA. If you want to return AA before BB and BB before CC,
use

=MID(x,LOOKUP(1E6,SEARCH({"CC","BB","AA"},x)),2)

If there are no instances of AA, BB or CC in x, the formula returns
#N/A.

Harlan thanks,
it is almost perfect with one exception. The strings i am looking for
contain various lengths. can you help me out with that too.


"Harlan Grove" wrote:

Nir wrote...
I tried both Search and Find but the problem is the I have few options for
the strint to search and these functions don not approve "OR" function.

They do, but it's tricky.

In other words I want to find "AA" Or "BB" Or "CC" within String "DFAA GTY".
Expected result would be "AA"

With "DFAA GTY" in a cell named x,

=MID(x,LOOKUP(1E6,SEARCH({"AA","BB","CC"},x)),2)

would return "AA". If your strings could have multiple instances of any
of these, this particular formula would return the left most instance
of CC first, then the leftmost instance of BB, and finally the leftmost
instance of AA. If you want to return AA before BB and BB before CC,
use

=MID(x,LOOKUP(1E6,SEARCH({"CC","BB","AA"},x)),2)

If there are no instances of AA, BB or CC in x, the formula returns
#N/A.

Nir wrote...
it is almost perfect with one exception. The strings i am looking for
contain various lengths. can you help me out with that too.

Always best to provide representative samples, e.g., looking for AAA,
BB or C in some string x in that order (return AAA first, then BB and
finally C).

=LOOKUP(1000000,SEARCH({"C","BB","AAA"},x),{"C","B B","AAA"})

thanks Harlan it works.
for that reason i placed a rephrased question in the forum + e.g.
thanks again


"Harlan Grove" wrote:

Nir wrote...
it is almost perfect with one exception. The strings i am looking for
contain various lengths. can you help me out with that too.

Always best to provide representative samples, e.g., looking for AAA,
BB or C in some string x in that order (return AAA first, then BB and
finally C).

=LOOKUP(1000000,SEARCH({"C","BB","AAA"},x),{"C","B B","AAA"})