Help

Cannot Trim Lookup from Linked Record

Topic Labels: Formulas
Solved
Jump to Solution
2353 5
cancel
Showing results for 
Search instead for 
Did you mean: 
Ryan_Reich
4 - Data Explorer
4 - Data Explorer

The TRIM function doesn’t seem to work when referencing a Lookup field in a Linked Record. Every time I try to trim the data that’s passed from a lookup in a linked record, my formulas return #Error. Here’s the formula at fault:

TRIM({AMID (from link)})&"-"&TRIM({AMID})
1 Solution

Accepted Solutions
ScottWorld
18 - Pluto
18 - Pluto

Welcome to the community, @Ryan_Reich!

Lookups are arrays, so they can’t be trimmed because they’re not a text string. But you can turn a lookup into a text string by adding empty text to it like this:

TRIM({AMID (from link)}&"")&"-"&TRIM({AMID})

See Solution in Thread

5 Replies 5
ScottWorld
18 - Pluto
18 - Pluto

Welcome to the community, @Ryan_Reich!

Lookups are arrays, so they can’t be trimmed because they’re not a text string. But you can turn a lookup into a text string by adding empty text to it like this:

TRIM({AMID (from link)}&"")&"-"&TRIM({AMID})
Ryan_Reich
4 - Data Explorer
4 - Data Explorer

Thanks for the response. I’m confused about why the lookup field is treated as an array when it seems like it’s just copying data from a cell attributed to another record. Shouldn’t it be treating that data the same as the original?

Lookup fields are an array because they are pulling data based on a LinkToAnotherRecord field, and those fields are arrays. Even if the record in question is only linked to one record, the value for a LinkToAnotherRecord field is always an array.

Welcome to the community, @Ryan_Reich! :grinning_face_with_big_eyes: I made a cheatsheet (a table) that lists all field types and how they’re treated by Airtable when rolled up. Most of the time they’re going to be arrays, but there are a few cases where Airtable treats them—for the purposes of formulas, at least—as single values when only a single record is looked up.

Thanks! This is really helpful!