How to highlight a cell if there is a specific word inside?

Question

I'm looking for something that can highlight all cells/strings that contains a specific string. I'm using Calc on LibreOffice.

Answer 1

You can use conditional formatting with a simple equal to criteria.

The trick is to use quotation marks around the search string.

Step-by-step:

1. Select a range of cells,
2. In the menu, select the Format > Conditional Formatting > Condition entry,
3. Enter the string you want to match surrounded with quotation marks,
4. Select the style to use for highlighting, of create a new one for that purpose,
5. Click OK.

Answer 2

My solution would be to use conditional formatting, but it's based on the assumption that the formatting you have at the moment can be affected.

I created a small spreadsheet with the following values in column A, starting from row 1 (i.e. there's no heading for the values): fred george barney george fred fred barney barney george fred barney george

In my example I want to highlight all the cells that contain the string "fred". To do this, select only one cell (e.g. A1 containing "fred" in my example) and then choose Format...Conditional Formatting... from the menu.

In an empty row change the values to "Formula is IF(SEARCH("fred",A1)>0,1,0)". This will look for the string "fred" in cell A1, and if it's found then applies the conditional format (btw, SEARCH isn't case sensitive). Next, change the formatting to whatever highlighting you want to use.

To future-proof your formatting I'd recommend using a cell reference instead of the literal string "fred". For example, store "fred" in cell Z1 or as a named cell somewhere else, and for the search function use "SEARCH(Z1,A1)". That way if you need to find a different string you can simply update cell Z1, rather than updating all of the conditional formatting again.

Once you've applied the conditional formatting to one cell, use the Format Painter to apply it to the others.

Hope this helps,

Bob.