I'm trying to use a script to automatically set up conditional formatting to highlight a specific set of names in a Google Sheet. The data comes from a Google Form where people enter their own name. The conditional formatting is triggered if both first and last names match the listed entries (in the example below, *Harry Potter and Ron Weasley).

The script coding looks like this:

.whenFormulaSatisfied('=OR(AND($D7="Potter",$E7="Harry"),AND($D7="Weasley",$E7="Ron"))')

Problem: If the person hits the spacebar after their name, or types Ronald instead of Ron or the like, then the highlighting doesn't work. I know I can't make it work if they make a typo entering either half of their name. I would, though, like it to still work if they hit the spacebar or don't abbreviate, etc. So, I want it to work as long as the specified text string is present.

Question: Is there a way to tweak this formula to make this work, even if the name doesn't 100% match? If so, how?

CodePudding user response：

Instead of strict comparison =, use REGEXMATCH.

For eg,

If the person hits the spacebar after their name, or types Ronald instead of Ron or the like,

Change

$E7="Ron"

to

REGEXMATCH($E7,"Ron.*")

• . Any character(like space or ald)
• * Previous regex repeated zero to unlimited times

See Reference - What does this regex mean?

REGEXMATCH returns TRUE or FALSE just like = does.