Check for Multiple Text Strings in a Cell

[David44357]

This formula will search the B cell for a word and post an indicator if it contains the keyword.
=IF(ISNUMBER(SEARCH("citations",B170)),"1", "")

My obstacle is I need four of these that each search for 3-4 different keywords.

I've tried things like:
=IF(ISNUMBER(OR(SEARCH("citations",B170)), SEARCH("sources",B170))),"1", "")

But I get errors. Does anyone know how to get a formula that checks a cell for multiple keywords? I keep trying to put the OR in different places without success.

-David

 

[Rick Rothstein]

I have tried this but it doesnt work can you please help

If you want help, you are going to have to tell us in what way it didn't work. Tell us what values you have in the referenced cells (named ranges) and what result you got and what result you expected. The more information you give us, the better chance of your getting an answer.

 

[Cindy Ellis]

Welcome to Mr. Excel!
What exactly are you trying to do? The solutions above provide a way to tell if a cell contains any of a specified set of possible strings. Is that what you want? Maybe you could post the list of words you're looking for, and a reference to the range you want to apply it to?

 

[vinz27]

If you want help, you are going to have to tell us in what way it didn't work. Tell us what values you have in the referenced cells (named ranges) and what result you got and what result you expected. The more information you give us, the better chance of your getting an answer.

Hi thanks for replying to the post

I have a column (Column B) with a list of products around 9000 of them. What i wanted to do was categorise them with keywords. i waned a formula that will search through column B for a key word, if that key word is in a cell for example in B5, i wanted a category word to be placed in cell C5. I have found a formula that will do 1 key word. but i want multiple key words to be searched and different categories for each key word. I have attached a sample excel document which has data.

I would really appreciate it if you could help me out.


[FONT=source_sans_proregular]https://mega.nz/#!SsB1BbDa[/FONT][FONT=source_sans_proregular]!ZgYegIarDUyBrTXaVMqd8Z6Udg3AWabqteI2l1O-Bck

[/FONT]thank you

 

[pvr928]

Hi vinz27

I second Rick Rothstein's post. This forum is an amazing resource of highly skilled and experienced Excel experts (I'm not including myself in that category). They are also incredibly generous with their suggestions, assistance and time. However, given the almost infinite number of unique solutions Excel can offer to any given circumstance, you have to be very clear in what you are asking for. I have found when asking these generous people for assistance I get the most help when I already have done my bit on problem solving the issue and present what is happening and what I want very clearly.

Firstly, I am sure the answers already given are, if not an exact solution to what you are seeking, very close to it. If I can be so bold, chandoo has come up with what appears to be a possible solution to what you are seeking.

How To Find Out If A Text Contains Question? [Excel Formulas] | Chandoo.org - Learn Microsoft Excel Online

With any solution offered, it often helps if you are prepared to do your own digging, not just beforehand, but also when interpreting the suggested solution. There is a plethora of free advice on the web now; it is just a matter of you spending your time in searching for it, analyzing and then applying it. Excel rewards those who spend time trying to master it.

Have fun !

Cheers

pvr928

 

[Cindy Ellis]

When I have needed to solve a similar problem, I created a new column for each of the pertinent keywords. Then, each "keyword" column could be filtered individually or in combination. You mentioned you knew how to search for a single term. If that's the case, just apply the same approach in added columns.

 

[Aladin Akyurek]

[...]

If I can be so bold, chandoo has come up with what appears to be a possible solution to what you are seeking.

How To Find Out If A Text Contains Question? [Excel Formulas] | Chandoo.org - Learn Microsoft Excel Online

[...]

Don't. Are you that sure that you inject "chandoo has come up with"? Many sites collect solutions from fora and post them, sometimes as simple re-writes. To an onlooker, they often appear as "owner" or "creator".

 

[Aladin Akyurek]

Hi thanks for replying to the post

I have a column (Column B) with a list of products around 9000 of them. What i wanted to do was categorise them with keywords. i waned a formula that will search through column B for a key word, if that key word is in a cell for example in B5, i wanted a category word to be placed in cell C5. I have found a formula that will do 1 key word. but i want multiple key words to be searched and different categories for each key word. I have attached a sample excel document which has data.

I would really appreciate it if you could help me out.


[FONT=source_sans_proregular]https://mega.nz/#!SsB1BbDa[/FONT][FONT=source_sans_proregular]!ZgYegIarDUyBrTXaVMqd8Z6Udg3AWabqteI2l1O-Bck

[/FONT]thank you

=LOOKUP(9.99999999999999E+307,SEARCH(keywords,$B2),keywords)

would categorize the input in column B in terms of the keyword list (keywords) that you set up.

 

[pvr928]

Don't. Are you that sure that you inject "chandoo has come up with"? Many sites collect solutions from fora and post them, sometimes as simple re-writes. To an onlooker, they often appear as "owner" or "creator".

Hi Aladin

If by "inject" you mean "inspected" then, yes, I have inspected the Web pages.

I subscribe to chandoo.org's email list (and I'm sure I'm one of many) - that's how I came to know his "q.words" solution. I found this remarkable in its simplicity, and I have adapted it to my own uses.

Chandoo does draw upon other contributors for his website, but to suggest he is simply repackaging ideas he has stumbled upon is to do him a great disservice. What is great about Chandoo is the extent he goes to in explaining his approach and how/why his solutions work. I have learnt a lot from him.

I recommend you subscribe!!

Cheers

pvr928

PS I am in no way affiliated with Chandoo - merely a fan!

 

[Aladin Akyurek]

[...]

PS I am in no way affiliated with Chandoo - merely a fan!

I stand by what I've said, even if you would be an affiliate...

 

[pvr928]

I stand by what I've said, even if you would be an affiliate...

Do you mean "even if you are _not_ an affiliate" Aladin? Otherwise I'm not following you.

You'll have to enlighten me as to the policy regarding linking to other Excel sites here, as plenty of people do it, and to many different fora, and no-one's objected that I have seen. I've done it before. Is Jon Peltier's site now off limits? Chip Pearson's?

Otherwise you simply appear to be inconsistent and arbitrary.