"Find()" function not finding a string within cell

Topic Labels: Formulas
3528 3
Showing results for 
Search instead for 
Did you mean: 
6 - Interface Innovator
6 - Interface Innovator

So I’m attempting to find the string “Active” as so:

Screenshot 2020-01-06 13.13.11

Yet it’s not picking up the string for a number of records (ex. should be 1 for the first record):
Screenshot 2020-01-06 13.13.21

Why is this happening? Thanks!

3 Replies 3
6 - Interface Innovator
6 - Interface Innovator

PS I’ve also not used the optional parameter of where to start, still have the same issue.

This is happening because of the format of the data in the field you’re searching. You’re using a lookup field, which returns an array. The FIND() function is looking for a string, and (sadly) doesn’t automatically convert an array into a string. You need to force the conversion by concatenating the target field with an empty string, like this:

FIND("Active", {status-contracts} & "")

Amazing! Makes so much more sense now. Thank you!