0

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?

TheMaster
  • 45,448
  • 6
  • 62
  • 85
MUNGHOLMI
  • 51
  • 5
  • Use `REGEXMATCH` – TheMaster Oct 12 '22 at 18:24
  • What happens if you have a Ronald and a Ronda, And the user types Ron? – TheWizEd Oct 12 '22 at 20:15
  • That shouldn't be a problem, TheWizEd, since it's partially an AND function. In the example above, it would only highlight the user's entry of *Ron* if the last name was also *Weasley*, and the list of names is not such that I have both a Ronald and Ronda Weasley. There's only going to be 20 or so names that it will look for in the entire dataset. – MUNGHOLMI Oct 13 '22 at 01:23
  • TheMaster, I wouldn't think REGEXMATCH does the trick, because it has to match two columns of data to trigger. So, it's only going to highlight *Ron* if the last name is *Weasley*. If it's *Ron*, but the last name field is *Burgundy*, it won't highlight. – MUNGHOLMI Oct 13 '22 at 01:25
  • Use the same logic in your question. – TheMaster Oct 13 '22 at 06:03

1 Answers1

2

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.

TheMaster
  • 45,448
  • 6
  • 62
  • 85