Len() and other functions not working on a linked field

[Chris_Guthrie]

Hello. I have tried to run Len() as well as some other functions, such as Left() and Right(), and isn’t working when referencing a linked field.

In my example, field Title has a forumula that measure the length of field Title Long. My formula is LEN({Title Long})

[W_Vann_Hall]

This isn’t covered too well in the documentation, but lookup fields referencing a text field are typically returned not as a string but as an array — even if the lookup returns only a single value. (What lookups referencing other field types return seemingly depends on how many values are returned: single-value returns appear to be in the type of the original field, while the rest come through as arrays. However, don’t quote me, as I’ve not tested this assumption exhaustively.)

For a non-text lookup, you can most easily check as what type it returns by selecting the ‘formatting’ tab; if Airtable informs you it can’t apply formatting, it’s probably as an array.

You can cast an array to a string by appending &'' to the field name. To cite your reference, LEN({Title Long}&'') should work as expected.

[Chris_Guthrie]

Awesome. Thank you. That worked!

[Penny_Norrie]

Thank you so much for this tidbit! I have been going in circles with the same type of problem but with SUBSTITUTE! Huge save for me. Thanks again!