Conditional formatting with or function, not case sensitive...

[Dan1000]

Is there a formula I can use to search cells in a column for say 3 specific words whether they be on their own or in a text string. I'd like to search for up to 10 words but perhaps this is not possible?!

I have used isnumber(find, but can not get it to work with the or function,

I have used the or function on its own but I can not get it to work,

I have used the countif function but can not get it to work with the or function



Any help would be much appreciated


Dan

 

[Peter_SSs]

Can you post the final Conditional Formatting formula that you used and state what is the first cell that it applies to? (not the first cell that is highlighted, but the first cell with the CF formula)

 

[Dan1000]

Can you post the final Conditional Formatting formula that you used and state what is the first cell that it applies to? (not the first cell that is highlighted, but the first cell with the CF formula)

The formula is as follows:

=COUNT(SEARCH("word1",M3),SEARCH("word2",M3),SEARCH("word3",M3)),

The first cell it applies to is:

M3 (M1 and M2 are my headers along with the rest of rows 1 and 2).


The problem I had was highlightling the cells at the start as I was advised to highlight the first cell the formula was to be placed in. However this dictates the cell that is being searched and I wanted the whole column to be searched and so I edited the first search box by placing $M$3:$M$4000 into the box myself, otherwise done by highlightling the whole column and then removing M1 and M2 then adding $ signs.

Having said all that, I find wierd search results when using conditional formatting formula. I really need to better understand what is happening as I still don't know why blank cells and cells without the search words appearred, I'm thinking forumla other than COUNT and SEARCH do not actually do what is required here perhaps?!

 

[Peter_SSs]

The formula is as follows:

=COUNT(SEARCH("word1",M3),SEARCH("word2",M3),SEARCH("word3",M3)),

Thanks for the formula. I was hoping for an actual formula and some actual (or realistic) sample data that I have been asking for.
The reason is that, depending on what you actual data is like (at the moment I have no idea), the CF formula you posted may not give you the results you want.

Your original post said you wanted to search the column for "words". This formula does not search for "words" in the traditional meaning.
Here is an example of that formula structure searching column M for "cat", "apple" or "dog". Whilst it correctly highlights M4 and M9, it falsely highlights M6 and M7 if you are looking for the words "cat" and "apple".

Excel Workbook
	M
3	pear
4	cat
5	house
6	Don was scathing
7	I like pineapples
8	plum
9	red dog
CF Sample 1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
M3	1. / Formula is =COUNT(SEARCH("cat",M3),SEARCH("apple",M3),SEARCH("dog",M3))	Abc

If that is an issue for you, then a little more is required:

Excel Workbook
	M
3	pear
4	cat
5	house
6	Don was scathing
7	I like pineapples
8	plum
9	red dog
CF Sample 2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
M3	1. / Formula is =COUNT(SEARCH(" cat "," "&M3&" "),SEARCH(" apple "," "&M3&" "),SEARCH(" dog "," "&M3&" "))	Abc

Further, if the column M cells can contain punctuation marks, the task of finding words is complicated further. If that is an issue for you, you may want to provide a small set of realistic sample data.

 

[Dan1000]

Thanks for the formula. I was hoping for an actual formula and some actual (or realistic) sample data that I have been asking for.
The reason is that, depending on what you actual data is like (at the moment I have no idea), the CF formula you posted may not give you the results you want.

Your original post said you wanted to search the column for "words". This formula does not search for "words" in the traditional meaning.
Here is an example of that formula structure searching column M for "cat", "apple" or "dog". Whilst it correctly highlights M4 and M9, it falsely highlights M6 and M7 if you are looking for the words "cat" and "apple".

CF Sample 1

	M
pear
house
plum

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:144px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]
[TD="bgcolor: #92d050"]cat[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]
[TD="bgcolor: #92d050"]Don was scathing[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]
[TD="bgcolor: #92d050"]I like pineapples[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]8[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]9[/TD]
[TD="bgcolor: #92d050"]red dog[/TD]

</tbody>

Conditional formatting

Cell Nr.: / Condition Format M3 1. / Formula is =COUNT(SEARCH("cat",M3),SEARCH("apple",M3),SEARCH("dog",M3)) <tbody> [TD="bgcolor: #92d050"]Abc

<tbody>

</tbody>

[/TD]

</tbody>

Excel tables to the web >> Excel Jeanie HTML 4


If that is an issue for you, then a little more is required:

CF Sample 2

	M
pear
house
Don was scathing
I like pineapples
plum

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:144px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]
[TD="bgcolor: #92d050"]cat[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]8[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]9[/TD]
[TD="bgcolor: #92d050"]red dog[/TD]

</tbody>

Conditional formatting

Cell Nr.: / Condition Format M3 1. / Formula is =COUNT(SEARCH(" cat "," "&M3&" "),SEARCH(" apple "," "&M3&" "),SEARCH(" dog "," "&M3&" ")) <tbody> [TD="bgcolor: #92d050"]Abc

<tbody>

</tbody>

[/TD]

</tbody>

Excel tables to the web >> Excel Jeanie HTML 4


Further, if the column M cells can contain punctuation marks, the task of finding words is complicated further. If that is an issue for you, you may want to provide a small set of realistic sample data.

Hi Peter

I think what you are saying is...

The formula searches for text strings whether they are specific words or parts of words - is that correct.

If that is correct...

Your comment remains valid and I appreciate your advice (no matter, I appreciate your advice!).

There are cells albeit only a few which have extensive text strings and therefore there is a risk of having some cells with the words embedded in text strings rather than specific, ie 'Pineapple' opposed to 'Apple', as you stated.

Now, whilst I am not too concerned about this, in this instance, as I can manage this in the data I have, I remain interested in the formula/s, some of which you have kindly shown, which may certainly help in the event that there are situations which need more precise evaluation, thank you.

With respect to the punctuation marks. I would be interested in knowing how to deal with these, as you indicate by saying 'if the column M cells can contain punctuation marks, the task of finding words is complicated further.'

With respect, apologies for not publishing the actual data. I'll do everything else otherwise to explain the detail in order to obtain a solution to any excel issue I may have, and visa versa.

 

[Peter_SSs]

I think what you are saying is...

The formula searches for text strings whether they are specific words or parts of words - is that correct.

Correct, for the original formula.

With respect to the punctuation marks. I would be interested in knowing how to deal with these ..

No perfectly simple solution. In general, dealing with text in Excel can be a quite tricky.
However, it comes down to what your actual data may be like, and only you know that at this stage.

If your text might only have 2 or 3 types of punctuation, say "." or "," or "?" immediately following a word, the the formula could be expanded to deal with that.

But if there can be lots of different punctuation and both before or after words then any formula solution becomes very large and not really feasible. Example:

Tom said: "I like apples (especially red ones) and pears, do you?" I replied that I do not. What a day!

Here, the revised formula that I posted would not find said, I, especially, ones, pears, you, not or day. And would you want to find "apple" when here it is just a plural, not like in pineapple?

There is further complication if you were searching for the words "cot" or "O'Connor" in "Tom O'Connor's cottage" as you would not want to find the cot in cottage but you may want to find O'Connor in O'Comnnor's. This one in particular is a problem since here you would want to be ignoring the second apostrophe (& the following s) but not the first apostrophe.

 

[Dan1000]

Correct, for the original formula.



No perfectly simple solution. In general, dealing with text in Excel can be a quite tricky.
However, it comes down to what your actual data may be like, and only you know that at this stage.

If your text might only have 2 or 3 types of punctuation, say "." or "," or "?" immediately following a word, the the formula could be expanded to deal with that.

But if there can be lots of different punctuation and both before or after words then any formula solution becomes very large and not really feasible. Example:

Tom said: "I like apples (especially red ones) and pears, do you?" I replied that I do not. What a day!

Here, the revised formula that I posted would not find said, I, especially, ones, pears, you, not or day. And would you want to find "apple" when here it is just a plural, not like in pineapple?

There is further complication if you were searching for the words "cot" or "O'Connor" in "Tom O'Connor's cottage" as you would not want to find the cot in cottage but you may want to find O'Connor in O'Comnnor's. This one in particular is a problem since here you would want to be ignoring the second apostrophe (& the following s) but not the first apostrophe.

I have dealt with these sorts of formula before and I can see what you mean. I keep excel formulas moderatlely simple. Longer / more complex formulas have more potential for errors which I prefer to avoid as I find that you can waste a lot of time trying to write out formulas and trying to correct the errors rather than complete the task at hand - still enjoy using and discovery new ways to short cut my way around Excel. Luckily my problem was not so word specific as needing either more specific word searching or attention to the punctuation in the formula.

Thank you for your help

 

[Peter_SSs]

Luckily my problem was not so word specific as needing either more specific word searching or attention to the punctuation in the formula.

Thank you for your help

No problem. Glad you have your immediate task sorted out.