Searching Text that contains particular WORDS.

[Abdullah Kajee]

Text in cell A1 " the quick brown fox"
Text in cel A2 " June is here"
Text in cell A3 " Today is Monday" and so on until row A55000.
I have a spreadsheet with thousands of lines but some lines of text repeats
as above. How do I write one IF statement that searches for one of three
words such as "brown", "here" and "Monday" and then returns the keyword that
I am looking for. This way, I can quickly filter on all lines that have the
word Monday or the word June etc..I know I can use
=IF(ISNUMBER(SEARCH(.......but help me to understand how to write 3 criteria
or more in one statement...right now I can only write one criteria ie
=IF(ISNUMBER(SEARCH("JUNE",A2)),"JUNE"

 

[Ron Rosenfeld]

Text in cell A1 " the quick brown fox"
Text in cel A2 " June is here"
Text in cell A3 " Today is Monday" and so on until row A55000.
I have a spreadsheet with thousands of lines but some lines of text repeats
as above. How do I write one IF statement that searches for one of three
words such as "brown", "here" and "Monday" and then returns the keyword that
I am looking for. This way, I can quickly filter on all lines that have the
word Monday or the word June etc..I know I can use
=IF(ISNUMBER(SEARCH(.......but help me to understand how to write 3 criteria
or more in one statement...right now I can only write one criteria ie
=IF(ISNUMBER(SEARCH("JUNE",A2)),"JUNE"

You can nest up to seven IF's:

=IF(ISNUMBER(SEARCH("JUNE",A2)),"JUNE",
IF(ISNUMBER(SEARCH("Brown",A2)),"Brown","not found"))

You can also use an array argument for the search term in your search
statement:

=CHOOSE(MATCH(TRUE,ISNUMBER(SEARCH({"Brown";"here";"Monday"},L1:L55000)),0),"Brown","Here","Monday")

ARRAY-Entered with <ctrl><shift><enter> will return the key word for the first
match.


But what if multiple key words are present? If in your 55000 lines, you might
have BROWN and HERE present.

You might want to investigate the Advanced Filter under the Data Menu.
--ron

 

[Abdullah Kajee]

Thank you so much Ron...I will try the nesting bit...the CHOOSE function
worked beautifully...the only problem is that I am getting a lot of #NA
results where there is no match...how do I write into the formula to leave
blank if no match?

 

[Abdullah Kajee]

RON,
Just to THANK YOU again and to let you know that I used the nesting of the
IF(ISNUMBER formula with a criteria to leave "" (blank) if no matches and it
works. Thank you very much...it is wonderful to have such a global community
for knowledge and information sharing. This is truly a connected community.

 

[Ron Rosenfeld]

Thank you so much Ron...I will try the nesting bit...the CHOOSE function
worked beautifully...the only problem is that I am getting a lot of #NA
results where there is no match...how do I write into the formula to leave
blank if no match?

There are many ways. One method:

=IF(ISNA(your_formula),"",your_formula)

and I think you're better off with the nesting approach. I've run into some
glitches using the SEARCH array approach and I don't have time to figure it out
right now.


--ron

 

[Ron Rosenfeld]

RON,
Just to THANK YOU again and to let you know that I used the nesting of the
IF(ISNUMBER formula with a criteria to leave "" (blank) if no matches and it
works. Thank you very much...it is wonderful to have such a global community
for knowledge and information sharing. This is truly a connected community.

You're very welcome. Thanks for the feedback.
--ron

 

[Tevuna]

Perhaps an auto filter would make a staight approach
Contains "June" OR Contains "Brown"

 

[Abdullah Kajee]

Thanks for responding Tevuna...your approach works as well...I just needed a
statement that I could use for managing more than 1000 line situations given
that EXCEL filters fail on the 1000th line.

 

[Gord Dibben]

Not true that filters fail on the 1000th row.

The autofilter drowdown list shows only 1000 items, but you can work around that
by using custom>contains or see Debra Dalgleish's site for further refinement.

http://www.contextures.on.ca/xlautofilter02.html#Limits


Gord Dibben MS Excel MVP

 

[Abdullah Kajee]

Thanks Gord...I will give it a try...but for my purposes, Ron Rosenfeld gave
me 2 solutions which suffice for my applicqtion...I need a formula rather
than a filter as I will be using the formula in SAP BW to return a specific
report...many thanks for repsonding...all inputs are value add...you learn
something new with each input.