Problems with TEXT functions on LOOKUP fields

Help! Text functions don’t seem to work with a lookup field - FIND always returns 0, SUBSTITUTE always returns “#ERROR!” and so on…

+18

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: LEFT({Lookup Field},5)
This will work as expected: LEFT({Lookup Field}&"",5)

This will work with all string/text functions including FIND, SUBSTITUTE, LEFT, RIGHT, etc

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: LEFT({Lookup Field},5)
This will work as expected: LEFT({Lookup Field}&"",5)

This will work with all string/text functions including FIND, SUBSTITUTE, LEFT, RIGHT, etc

That’s a great workaround - thanks so much!

+2

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: LEFT({Lookup Field},5)
This will work as expected: LEFT({Lookup Field}&"",5)

This will work with all string/text functions including FIND, SUBSTITUTE, LEFT, RIGHT, etc

Thank you for this. I was at my wit’s end when I came across this

+1

Wow, this is quite bad. :frowning:

Sign up