Left formula erroring - what am I doing wrong?


#1

I’m trying to do a very simple Left formula to extra the first three letters of a lookup text field but keep getting errors. Is there something obvious I’m missing here:


#2

I’m new to Airtable…but I believe the issues is that the field “ASX Code” is a Lookup field type.


#3

The same thing happens with a Rollup field


#4

Both Lookup and Rollup field types are Arrays. Try this formula…

LEFT(ARRAYJOIN({ASX Code}),3)

#6

Thanks Wallace. That seems to have done something, but now only the first character is displayed:


#7

Try this formula…

LEFT({ASX Code}&"",3)

#8

Thanks so much for your help.

So I’ve fixed it, but my fix doesn’t make any sense. I used LEFT({ASX Code}&"",5), which seemed to have picked up the additional characters.



#9

Strange? Glad you were able to figure it out!


#10

Lookup fields are arrays, internally — even if they return only a single value. (As @Andre_Zijlstra mentioned, rollups also seem to be, as well — but since rollups are usually accessed through aggregation functions that return a string, it’s less often a problem.) To be able to access the value of a lookup field using a string-processing function such as LEFT(), it’s necessary to convert the lookup’s array value to a text value, and, as you discovered, the easiest way to do so is to concatenate it with a string by adding "&''" to the formula.

Edit: That said, LEFT({ASX Code}&'',3) should work; having to specify 5 characters means something is wrong and may rise up to bite you at a later date. Could you test something for me? Change your row height (that’s the icon with the lines and double-headed vertical arrow) to extra tall and tell me if {Code Shortened} appears on the top row or midway down the cell. Thanks.