Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I am trying to use conditional formatting to highlight cells in a column that contain an exact word with Excel 2012. I am only having partial success using "cells that contain text" function. The issue is that I want to highlight only those cells that contain the exact word "tea". However, cells that contain the word "instead" are also highlighted because the word "tea" is inside "instead", which I don't want. I only want to highlight cells that have "tea" in them. Is this possible.

Another thing is that I don't want any capitalization bias. I want instances of "tea", "Tea", and "TEA" to be highlighted.

share|improve this question
up vote 2 down vote accepted

To find an exact word in a cell, you're going to have to use a VBA function, there doesn't seem to be a way to do this with Excel functions alone.

Specifically, the following function seems to work well:

Function ExactWordInString(Text As String, Word As String) As Boolean
    ExactWordInString = " " & UCase(Text) & " " Like "*[!A-Z]" & UCase(Word) & "[!A-Z]*"
End Function

Source: Spreadsheet Page Excel Tips: Is A Particular Word Contained In A Text String?

To use it:

  1. Hit Alt+F11 to open the VBA editor
  2. In the Project window, right-click and select Insert → Module
  3. Paste the code above
  4. Back in the workbook, highlight your cells and go to Conditional Formatting → New Rule...
  5. Select "Use a formula to determine which cells to format"
  6. Enter the following formula: =ExactWordInString(A1, "tea")
  7. Click "Format..." and select your formatting options, then click OK twice

That should do the trick.

share|improve this answer
Thanks for replying. I tried this option before also, and just tried again, but it still highlights cells with "instead" in them – Darren Haynes Oct 10 '12 at 12:19
@Darren See updated post with screenshots. Are you sure you tried that exact option? I can't imagine it would work differently in Excel 2010. – Indrek Oct 10 '12 at 12:22
Hi, yep I tried that but didn't work. Here is a screen cast showing the 3 options I tried. – Darren Haynes Oct 10 '12 at 12:29
@Darren This may be a long shot, but did you actually clear the formatting from the first attempt? I noticed that on the third try you seemed to set a much darker background colour than the rows you then pointed at, which might mean that the "Text that Contains" formatting rule is still active. – Indrek Oct 10 '12 at 12:35
Hey there - after each fomatting attempt i hit "ctrl+z" to undo the action, but of course you can't see that on the screen. That does clear out the highlighting in the cells before i try the next formatting option. Does that mean the rule has been removed too? – Darren Haynes Oct 10 '12 at 12:41

In Format only cells that contain, you can use Cell value and choose equal to, and insert "tea" in the last field.

share|improve this answer
Thanks for replying. I tried this option before, and just tried again, but it still highlights cells with "instead" in them – Darren Haynes Oct 10 '12 at 12:19
Weird, it works here... Then I don't know! – m4573r Oct 10 '12 at 12:30
Worked for me (Excel 2010). – Rory Solley Jun 16 '15 at 9:24

Do you have any punctuation in the cells? If not then this formula-based solution should work for you:

Assuming data in column A, select that column and apply conditional formatting using the "Use a formula to determine which cells to format" option, with this formula

=SEARCH(" tea "," "&A1&" ")

SEARCH is not case-sensitive. Formula searches for "tea" with a space either side, within text contents with a space either side, so that will capture Tea at the start, or tea at the end but not Team or instead

Punctuation can mess this up as it won't find tea, (with an immediately following comma) for example. To cater for that you can add SUBSTITUTE functions to the formula to remove those, e.g. if you might have commas and full-stops (periods) use this version

=SEARCH(" tea "," "&SUBSTITUTE(SUBSTITUTE(A1,",",""),".","")&" ")

share|improve this answer

under conditional formatting, use the "Use a formula to select which cells to format" option.


=AND(NOT(ISBLANK(A*)), A* = "tea") (where A* is the 1st cell in the range you want to format)

add a second rule for the same range and enter the formula:


Make sure that the 1st rule (selecting for "tea" is 1st (on top)

This will make any cell that's not blank format according to the second rule, and then any cell that contains only "tea" will format according to the 1st rule.

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .