Hello! I have a pretty basic formula that isn't working for some reason. Basically, I'm trying to see if a substring I'm extracting with a REGEX formula, and then trimming, is contained inside another string.
Here is my formula:
FIND({Formula Field of String to Search For}, "" & {Lookup Field of String to Search Inside})
The second parameter seems fine: I know that the lookup field needs the ["" &] to force it to render as a string instead of an array, and that's working. The issue I'm having is with the first parameter, the string to search for.
This formula field that the first parameter is referencing a REGEX_EXTRACT() on a long text field, that I need to edit (either using LEFT() or TRIM()). My goal is to then search for this extracted string in the string generated from the lookup field, using FIND(). However, no matter what I do, the first parameter it seems like it isn't accepted as a string within FIND(). I've tried UPPER(), CONCATENATE(), and the "" & trick, but no matter what FIND() just returns 0.
The strange thing is, when I put in a string manually instead of trying to use this formula in the first parameter, the FIND() function works fine-- so I know it's an issue with the first parameter. And the string always displays fine inside the formula field I'm referencing for the first parameter, it just won't seem to work inside the FIND() formula.
Very grateful for any help!
