Conditional formatting based on two strings in one cell

boglet

New Member
Joined
Jul 26, 2021
Messages
5
Office Version
  1. 2019
Platform
  1. Windows
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
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,922
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
How about
Excel Formula:
=AND(ISNUMBER(SEARCH("/one/","/"&A2&"/")),ISNUMBER(SEARCH("/three/","/"&A2&"/")))
 
Solution

boglet

New Member
Joined
Jul 26, 2021
Messages
5
Office Version
  1. 2019
Platform
  1. Windows
Hi & welcome to MrExcel.
How about
Excel Formula:
=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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,922
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jul 26, 2021
Messages
5
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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
1ONE/TWO/THREE/FOUR
2ONE/THREE/FOUR
3TWO/FOUR
4ONE/FOUR
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A1:A4Expression="AND(ISNUMBER(SEARCH(""/one/"",""/""&A2&""/"")),ISNUMBER(SEARCH(""/three/"",""/""&A2&""/"")))"textYES


This is the Conditional formatting dialog:

1627298556878.png


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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,562
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
The formula is:

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


not:

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

boglet

New Member
Joined
Jul 26, 2021
Messages
5
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

The formula is:

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


not:

Excel Formula:
="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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,922
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jul 26, 2021
Messages
5
Office Version
  1. 2019
Platform
  1. Windows
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
1ONE/TWO/THREE/FOUR
2ONE/THREE/FOUR
3TWO/FOUR
4ONE/FOUR
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A1:A4Expression=AND(ISNUMBER(SEARCH("/one/","/"&A1&"/")),ISNUMBER(SEARCH("/three/","/"&A1&"/")))textYES


Thank you so much for your help (and patience)


Have a great day
Boglet
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,922
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Forum statistics

Threads
1,144,236
Messages
5,723,177
Members
422,480
Latest member
RCWh3rry

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top