Google Sheets – Conditional Formatting with Custom Formula

conditional formattinggoogle sheets

I have pretty much zero experience with spreadsheets but I've got what I think is a simple task. I've been keep a log of how I spend my time. I've codified the language so I can easily grab the information for conditional formatting. I don't want to change the cell it's in because that will have other bits of information. So I'm using a custom formula to change the cells in column C. I searched around and found a formula in an article. I copied and pasted and altered what I thought I needed. I got it to work but the problem is it only catches the string of characters if they at the beginning of the text. I need to know how to get the string of text from anywhere in the cell for the other words to be input for other conditional cells.

Here is the formula I found:

 =REGEXMATCH($C3, "\AGeorge\s*([^\n\r]*)")

Here's how I altered it:

 =REGEXMATCH($B1, "\Aate\s*([^\n\r]*)")

Any help would be greatly appreciated and thanks in advance!

Best Answer

If you're a beginner, I'd skip REGEX formulas and go with something simpler like this:

=ISNUMBER(SEARCH(" ate"," "&$B1))

If you're wondering why the space before " ate" and appended to the beginning of the cell reference, it's to help assure that words that just happen to have "ate" in them don't cause a TRUE (e.g., "drank water" has "ate" in it, but it's not what you mean).

Related Topic