12

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

pnuts
  • 58,317
  • 11
  • 87
  • 139
Videl
  • 840
  • 2
  • 7
  • 18
  • You can use [filters](https://help.libreoffice.org/Calc/Filter:_Applying_Advanced_Filters) to filter the cells with a specific string (you can use a simple find or even regex) and then highlight it. – Alex Jul 15 '13 at 07:47
  • I managed to get it to work on a little part, but I have many rows, that's not quite easy.. – Videl Jul 15 '13 at 09:32

2 Answers2

15

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.

Example

Laurent Couvidou
  • 32,354
  • 3
  • 30
  • 46
  • It seems that it has to be double quotes, as shown in this answer. For some reason I encountered behavior in OpenOffice where the software was putting in single quotes, which fails. – Brick Feb 07 '17 at 17:32
  • I would like asked if in conditional statement can be the cell format. For example: If the background color of this cell is red, type "red" in this cell. Thx for info. – Stilgar Dragonclaw Jan 17 '21 at 19:13
1

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.

boblaoshi
  • 46
  • 4