Left formula erroring - what am I doing wrong?

[HostKeep_SysAdm]

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:

[Wallace_Miller]

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

[Andre_Zijlstra]

The same thing happens with a Rollup field

[Wallace_Miller]

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

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

[HostKeep_SysAdm]

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

[Wallace_Miller]

Try this formula…

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

[HostKeep_SysAdm]

Wallace_Miller:

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

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.

[Wallace_Miller]

Strange? Glad you were able to figure it out!

[W_Vann_Hall]

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.

[Alex_Solak]

I just wanted you to know that I love you.
Ran into the same thing today with lookup fields and now know that arrays will break a lot of functions (Like say, DATETIME_PARSE.