Help! Text functions don’t seem to work with a lookup field - FIND always returns 0, SUBSTITUTE always returns “#ERROR!” and so on…
I agree this isn’t useful or expected behavior.
However there is a workaround—append (or prepend) an empty string to your field reference and it will work.
This will produce an error:
This will work as expected:
This will work with all string/text functions including FIND, SUBSTITUTE, LEFT, RIGHT, etc
That’s a great workaround - thanks so much!