
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Apr 06, 2017 07:01 AM
Help! Text functions don’t seem to work with a lookup field - FIND always returns 0, SUBSTITUTE always returns “#ERROR!” and so on…

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Apr 06, 2017 08:49 AM
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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Apr 06, 2017 11:02 AM
That’s a great workaround - thanks so much!

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Aug 15, 2019 06:12 PM
Thank you for this. I was at my wit’s end when I came across this

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Feb 01, 2021 10:10 PM
Wow, this is quite bad. :frowning:
