Help

The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.

Problems with TEXT functions on LOOKUP fields

3584 4
cancel
Showing results for 
Search instead for 
Did you mean: 
Alan_Larsen
5 - Automation Enthusiast
5 - Automation Enthusiast

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

4 Replies 4

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!

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

Tim_M_GT
5 - Automation Enthusiast
5 - Automation Enthusiast

Wow, this is quite bad. :frowning: