Search a cell for specific 'special characters' and color the cell red if found

excel conditional-formatting
Patje picture Patje · Sep 27, 2013 · Viewed 56k times · Source

I want to search a column of cells for any of these special characters:

` ‘ “ \ / ^

If present (that can also be like C2="fruit\"), I want the cell to return a red color.

I know I should use conditional formatting for this and a find function... but how do I combine it with all the characters?

Thx for the help.

Answer

Siddharth Rout picture Siddharth Rout · Sep 27, 2013

To do this via Excel formulas, you will have to use Helper Columns.

Let's say you have some text in Col A as shown in the pic below

enter image description here

Next Type your special characters say in E1:E6

Next in B1, type this formula

=SUM(IF(ISERROR((FIND($E$1:$E$6,A1))),0,1))

and press CTRL + SHIFT + ENTER and copy the formula down.

enter image description here

Next highlight COl A and in Home | Conditional Formatting | New Rule, Select Use a Formula to determine which cells to format

Next Type the formula =$B1>0 as shown in the screenshot and you are done

enter image description here

This is the output

enter image description here

Hide Col B/E if needed.

Related questions

Conditional Formatting (IF not empty)

How do I conditionally format a cell so if not blank it is grey? I tried to do 'not equal', but it didn't work. I am using Windows Office 2003 with Windows XP at work. I don't see the same feature …

Read More
Change the color of cells in one column when they don't match cells in another column

I want to check if the values in one column are the same as values in another column. Whenever the values are not the same, I would like to change the color of these cells. For example: Column I, Column …

Read More
Conditional Formatting using Excel VBA code

I have Range object called DistinationRange which contains reference to range B3:H63 I want to apply the following two conditional formatting rules using Excel VBA code dynamically. (Because the range would not be same all the time) If Cell …

Read More