Regex for variations of a phrase

I have a text column for comments (see example below), and I need to know (True/False) whether it contains the misrun or misfire. Misrun and misfire can be spelled or annotated with special characters. I currently have a contains formula trying to find all the different spellings, but I know this is not a good way to do it. Note, misrun and misfire can occur anywhere in the string.

I know I can use the Regex tool set to Match to give me the true/false, but I don't know enough regex to write it. Help would be greatly appreciated. Thank you.

Regex

Accepted answers

estherb47

Hi @FreeRangeDingo,

Interesting challenge!

What about something along the lines of

Regex_countmatches([field],“mis.*run”)>0

For misfire and variants, try mis.*fire

Simplistic (and not on my computer to check), but should find all variations of both words.

The challenge that I’ve had with regex_match is that the contents of the entire field have to match the code. Regex count matches finds how many matches to a code are within a field.

Please let me know if that helps.

Cheers!
Esther

FreeRangeDingo

@estherb47 this is the best solution so far. I wasn't able to make the others work, so I was back with my super clunky contains formula. That formula filtered out 172 records from my data set. Your solution filtered out 173 without the clunk. I also learned something new about regex_match versus regex_countmatches. Thank you very much!

All comments

Thableaus

Hi @FreeRangeDingo

I think this is the kind of situation you could use some Fuzzy Match + data prep to get what you want, because writing a perfect REGEX could rbe a little trouble, since there might be a lot of variations.

Cheers,

joshuaburkhow

Couldn't you just simplify this to either contains "run" or "fire" then you don't need all those combinations.

MarqueeCrew

You could try:

Regex_match([perfstatus comment],"\bmis[^a-zA-Z]*?run\b")

The search will find words beginning with MIS that may be followed by non-letters followed by run and ending the word.

Cheers,

Mark