find string within other string

[Guest]

I need to find multiply strings within ONE string

 

[Bob Phillips]

Look at Data>Text To Columns

--
HTH

Bob Phillips

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

 

[Guest]

Have you considered the SEARCH function?

 

[Guest]

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"

 

[Guest]

Bob
Please elaborate as i tried that one too.

Look at Data>Text To Columns

--
HTH

Bob Phillips

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

 

[Harlan Grove]

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.

 

[Guest]

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]

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","BB","AAA"})

 

[Guest]

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