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.

4 Answers

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.

14

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

3

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

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

enter:

=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:

=NOT(AND(ISBLANK(A*))

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.

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy