Help

Re: Left formula erroring - what am I doing wrong?

1446 1
cancel
Showing results for 
Search instead for 
Did you mean: 
HostKeep_SysAdm
5 - Automation Enthusiast
5 - Automation Enthusiast

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:
Screenshot 2018-07-16 09.50.10.pngScreenshot 2018-07-16 09.50.04.png

9 Replies 9
Wallace_Miller
5 - Automation Enthusiast
5 - Automation Enthusiast

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

The same thing happens with a Rollup field

Wallace_Miller
5 - Automation Enthusiast
5 - Automation Enthusiast

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

LEFT(ARRAYJOIN({ASX Code}),3)
HostKeep_SysAdm
5 - Automation Enthusiast
5 - Automation Enthusiast

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

Wallace_Miller
5 - Automation Enthusiast
5 - Automation Enthusiast

Try this formula…

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.

image.png
image.png

Wallace_Miller
5 - Automation Enthusiast
5 - Automation Enthusiast

Strange? Glad you were able to figure it out!

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.

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.