Using conditional formatting for exact match text

Question

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.

Answer 1

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.

Answer 2

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,",",""),".","")&" ")

Answer 3

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