Finding a word in a comments field

[MKM]

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!!

[JoeM · Accepted solution]

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!

[Storm]

You could populate the below into a FILTER tool (or modify it for a FORMULA tool):

contains([your_field_here],'7')
and
uppercase(substring(replace([your_field_here],' ',''),findstring(replace([your_field_here],' ',''),'7')+1,1)) = 'D'

This SHOULD return any record where your_field_here contains a 7 followed by either a D (upper or lower case) or any number of spaces followed by a D (upper or lower case).

Please, let me know if there's a flaw here...

[MKM]

Thanks for the reply Storm! Unfortunately, that still didn't seem to work. Does the location within the comments field matter? For example, what if the comments go "Important 7D and 15 hours" 7 isn't the first part of the comments, its in the middle. 

Thanks again for the help!!

[Storm]

Hi MKM:

No, position in the field shouldn't matter.

Curious:  when you say it still doesn't work - which value(s) do you have that it flags incorrectly? I tested it on these 9 sample values:

7 days
this row contains 7D
7 d
7d
there are also 7 DAYS here
other days such as 7 should not work
8 Days
7 dAyS
4

...it correctly identifies all but "other days such as 7 should not work", "8 Days" and "4" as meeting the rule, and it excludes them. What value(s) are you using that it flags incorrectly?

It occurred to me after I posted last night that the "contains([your_field_here],'7')" portion, is redundant. Simply having this in the filter:

uppercase(substring(replace([fieldhere],' ',''),findstring(replace([fieldhere],' ',''),'7')+1,1)) = 'D'

...worked fine for me, because it tests (should, anyway) for everything - both a "7" being there and a "d" immediately following it, either with or without some spaces between them.

[JohnJPS]

Would the following hit all your cases?

REGEX_CountMatches([txt], "[0-9]+\<Days\>") + 
REGEX_CountMatches([txt], "[0-9]+D") +
REGEX_CountMatches([txt], "[0-9]+ \<Days\>") + 
REGEX_CountMatches([txt], "[0-9]+ D")
> 0 

 Assign to a Boolean variable: should be true if there's a match, else false.

[JoeM]

If you are looking for a regex, try the following in the formula tool. 

IF REGEX_CountMatches([Field1], "(\d+)\s?d|days?")>0 THEN "Match" 
ELSE "No Match" 
ENDIF

[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!!