How can I write "and" in conditional formatting?

Hello!

How can I write "and" in the attached photo? So I don't have to click again and again and write another word because it is pretty time consuming.

Conditional formatting > highlight cells rules > text that contains

Thank you!

Hi there.

A picture is worth 1,000 words. An Excel sheet is worth 1,000 non-editable pictures.

Please read the yellow banner about sample worksheets, at the top of the screen. Act on its guidelines and post a SMALL sample sheet complete with an explanation and some expected results.

Hello!

I attached the file.

So, is there a way to write a formula and highlight all the phrases that contain that word?

For example, what formula could I write to highlight all the phrases that contain the words "Bucuresti" or / and "Brasov"? Only in the "A" column.

PS: Bucuresti and Brasov are cities from Romania.

Thank you!

Like this ? Formula is =OR(ISNUMBER(SEARCH("brasov";$A2));ISNUMBER(SEARCH("Bucuresti";$A2))) ( using European settings)

Originally Posted by Pete_UK

Select all the cells from A2:A2920 then click on Conditional Formatting | New Rule | Use a formula... , then put this formula in the dialogue box which pops up:

=COUNTIF($A2,"*"&"Bucuresti"&"*")+COUNTIF($A2,"*"&"Brasov"&"*")

Note that you may need to use semicolons ( ; ) instead of commas ( , ) in the formula, and translate the COUNTIF function to your language, depending on your regional settings.

Click on the Format button | Fill tab and choose your colour, then OK twice to exit the dialogue box.

There are 51 of them in that range, which should be highlighted if you scroll down.

Hope this helps.

Pete

Hello,

Would you like to tell me where is the mistake? I press OK and then I try to sort, but there is no green color...

// LATER EDIT: I use "," instead of ";". My settings for language are for USA. Also, I am using licensed Microsoft 365 if it helps...

// LATER EDIT 2: @Le Mokko, formula "=OR(ISNUMBER(SEARCH("brasov";$A2));ISNUMBER(SEARCH("Bucuresti";$A2)))" does not work. I replaced ";" with ",". It sorts words with or without the targeted words (Bucuresti or Brasov).

You applied it to the entire column (not a great idea), so your formula should refer to $A1 not $A2

Formula:

Please Login or Register  to view this content.

Works fine for me. So when you opened the sheet you got errors?

As I said, the formula should refer to $A1 not $A2:

=COUNTIF($A1,"*"&"Bucuresti"&"*")+COUNTIF($A1,"*"&"Brasov"&"*")

since you applied it to a range starting with row 1 not row 2.