Searching Text that contains particular WORDS.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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"
 
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"
Click to expand...


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
 
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?
 
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.
 
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?
Click to expand...

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,
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.
Click to expand...

You're very welcome. Thanks for the feedback.
--ron
 
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.
 
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.
 
Back
Top