Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

3 Answers 3

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 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. screencast-o-matic.com/watch/cl66lO93h –  Darren 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 Oct 10 '12 at 12:41

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

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 Oct 10 '12 at 12:19
    
Weird, it works here... Then I don't know! –  m4573r Oct 10 '12 at 12:30

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.