N
Nir
I need to find multiply strings within ONE string
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
B
Bob Phillips
Look at Data>Text To Columns
--
HTH
Bob Phillips
(replace somewhere in email address with gmail if mailing direct)
--
HTH
Bob Phillips
(replace somewhere in email address with gmail if mailing direct)
B
Barb Reinhardt
Have you considered the SEARCH function?
N
Nir
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"
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"
N
Nir
Bob
Please elaborate as i tried that one too.
Please elaborate as i tried that one too.
Bob Phillips said:Look at Data>Text To Columns
--
HTH
Bob Phillips
(replace somewhere in email address with gmail if mailing direct)
H
Harlan Grove
Nir wrote...
They do, but it's tricky.
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.
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.
N
Nir
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.
it is almost perfect with one exception. The strings i am looking for
contain various lengths. can you help me out with that too.
H
Harlan Grove
Nir wrote...
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"})
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"})
N
Nir
thanks Harlan it works.
for that reason i placed a rephrased question in the forum + e.g.
thanks again
for that reason i placed a rephrased question in the forum + e.g.
thanks again