Conditional formatting based on two strings in one cell

[boglet]

Hello All,

I need to format a cell based on the contents of the cell, for example the cell contains the following text:

ONE/TWO/THREE/FOUR

Easy enough to use conditional formatting by matching one word - the cell will higlight green if it contains the word ONE and it will highlight red if it contains the word THREE

But I also need to match on two of the words - so the cell will highlight orange if it contains the word ONE and the word THREE

I have found several formulas for use within conditional formatting, none of which seem to do what I want

Can anyone suggest a formula I could use in conditional formatting to make the above work?

Thanks in advance for any suggestions

 

[Fluff]

Hi & welcome to MrExcel.
How about

=AND(ISNUMBER(SEARCH("/one/","/"&A2&"/")),ISNUMBER(SEARCH("/three/","/"&A2&"/")))

 

[boglet]

Hi & welcome to MrExcel.
How about

=AND(ISNUMBER(SEARCH("/one/","/"&A2&"/")),ISNUMBER(SEARCH("/three/","/"&A2&"/")))

Hi Fluff


Thanks for replying so quickly, I have tried similar solutions but, like those, I could not get yours to work

The text I have in the cell (including the apostrophe) is:

'ONE TWO THREE FOUR

The other conditional formatting is done using the "Cell contains" option and works fine


Many thanks,
Boglet

 

[Fluff]

That example is totally different than your original post, so please post some accurate samples of your data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[boglet]

That example is totally different than your original post, so please post some accurate samples of your data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Yes, my apologies, I have corrected the cell contents to match what you provided the example for and cannot get it to work - I must be doing something wrong

I have installed the XL2BB add-in and have the result:

Boglet.xlsx
	A
1	ONE/TWO/THREE/FOUR
2	ONE/THREE/FOUR
3	TWO/FOUR
4	ONE/FOUR
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:A4	Expression	="AND(ISNUMBER(SEARCH(""/one/"",""/""&A2&""/"")),ISNUMBER(SEARCH(""/three/"",""/""&A2&""/"")))"	text	YES

This is the Conditional formatting dialog:

And this is the contents of the formula which should be as you kindly provided:

="AND(ISNUMBER(SEARCH(""/one/"",""/""&A2&""/"")),ISNUMBER(SEARCH(""/three/"",""/""&A2&""/"")))"


Hope that helps

 

[RoryA]

The formula is:

=AND(ISNUMBER(SEARCH("/one/","/"&A2&"/")),ISNUMBER(SEARCH("/three/","/"&A2&"/")))

not:

="AND(ISNUMBER(SEARCH(""/one/"",""/""&A2&""/"")),ISNUMBER(SEARCH(""/three/"",""/""&A2&""/"")))"

 

[boglet]

The formula is:

=AND(ISNUMBER(SEARCH("/one/","/"&A2&"/")),ISNUMBER(SEARCH("/three/","/"&A2&"/")))

not:

="AND(ISNUMBER(SEARCH(""/one/"",""/""&A2&""/"")),ISNUMBER(SEARCH(""/three/"",""/""&A2&""/"")))"

You are correct, however once the formula is pasted into the formula field in the conditional formatting dialog, Excel adds the extra quote marks all by itself

Which you can see in the XL2BB generated code:

 

[Fluff]

Just delete the 1st & last set of quotes.
Also as your data starts in A1 you need to change the cell reference in the formula.

 

[boglet]

Just delete the 1st & last set of quotes.
Also as your data starts in A1 you need to change the cell reference in the formula.

Thanks Fluff, I don't know why Excel put the extra quotes in there and for other formulas I have used it didn't seem to make a difference

In those other formulas I have also tried removing the quotes and it always seemed to put them back in so I have come to ignore them

In this case I have removed them and for some reason it seems top have stuck. I have also removed all the other ones it assed and now the conditional formatting works:

Boglet.xlsx
	A
1	ONE/TWO/THREE/FOUR
2	ONE/THREE/FOUR
3	TWO/FOUR
4	ONE/FOUR
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:A4	Expression	=AND(ISNUMBER(SEARCH("/one/","/"&A1&"/")),ISNUMBER(SEARCH("/three/","/"&A1&"/")))	text	YES

Thank you so much for your help (and patience)


Have a great day
Boglet

 

[Fluff]

Glad we could help & thanks for the feedback.