Finding a word in a comments field

Hi everyone!

I feel like this should be pretty easy, but haven't found a way to do this. I'd like to create a formula that, as one part of the formula, allows me to search for specific letter(s) in comments field of a data source.

As an example, imagine the comments field is: "7 Days and 15 hours until December". I'd like to create a formula that indicates that this row has "7 Days" in it.

First, I tried using "Contains" but it doesn't seem to be distinguishing the 7 Days. I tried using the Regex function but not sure that works properly in this case either, so not sure what is the best approach for being able to pull that out from a text string like the one I made above.

Any suggestions appreciated!!

Common Use Cases

Accepted answers

JoeM

Try the following formula:

FindString([FieldName], "7 days")

If the result is -1, then it was not found. If it was found, it would return a number of 0 or greater - which represents the position it was found. Note that it is case-sensitive.

All comments

JoeM

Try the following formula:

FindString([FieldName], "7 days")

If the result is -1, then it was not found. If it was found, it would return a number of 0 or greater - which represents the position it was found. Note that it is case-sensitive.

JohnJPS

The following in a formula should return the "7" if there's a match, otherwise will return the original string:

REGEX_Replace([txt], "([^0-9]*)([0-9]+) (Days)(.*)", "$2")

MKM

Thanks for the reply JoeM!

Is there any way to adjust this formula to make it more "open-ended" for lack of a better term? The comments section I'd be searching is manually entered, so some people might put "7 Days" some might put "7 days" some might put "7D" or "7d"

Is there anyway to check if there's any type of 7 & d combo?

Thanks!

MKM

Thanks everyone for the help!! Surprisingly, when I tried the FindString again before trying the others (figured I'd redo them all today), it worked. Not sure what I did differently, but it stuck. But thanks for all the other suggestions! I had never used the Regex function, it seems pretty cool. I honestly didn't know it existed. A lot of stuff we do is sorting through manually inputted comments field so that could be huge for us

Thanks again!!

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703