This Product Ideas board is currently undergoing updates, but please continue to submit your ideas.


Showing results for 
Search instead for 
Did you mean: 
5 - Automation Enthusiast
5 - Automation Enthusiast

I would like to use a ARRAY_FIRST() and a ARRAY_LAST() function, taking an array as argument, and resulting in the first (or the last) element of this array.

For now, I have to ARRAY_JOIN(my_array, ",") first, and the look after the string before the first “,”… which is a little tricky and need 2 formulas.

ARRAY_JOIN(my_array, ",") → my_joined_array (formula 1)
IF(FIND(',', my_joined_array) = 0, my_joined_array, LEFT(my_joined_array, FIND(',', my_joined_array) - 1)) → the final value (formula 2)

I would like to write ARRAY_FIRST(my_array) as an unique formula…



5 - Automation Enthusiast
5 - Automation Enthusiast

This is great! Way better than the adapted solution I had above (and actually makes sense to me).

5 - Automation Enthusiast
5 - Automation Enthusiast

Hard to believe Array maniuplation are still not possible in 2022. Such a pain

11 - Venus
11 - Venus

Maybe it would bring new ideas. Explanation is simple - SUBSTITUTE has 4th parameter - number of the occurence. If omitted, it changes all. That’s why it used often in formulas, where it seems nothing to substitute. Like here, with FIND, to Find last comma.

18 - Pluto
18 - Pluto

Now that regular expressions are available, getting the first or last item from an array is a lot easier (at least when the items don’t contain commas):

First item:

IF(values, REGEX_EXTRACT(ARRAYJOIN(values), "[^,]*"))

Last item:

IF(values, REGEX_EXTRACT(ARRAYJOIN(values), "[^,]*$"))

Screen Shot 2022-09-08 at 7.38.25 PM

16 - Uranus
16 - Uranus

Clever. Note that you can still handle Rollup arrays with commas by using ARRAYJOIN(values, 'some other deliminator')

4 - Data Explorer
4 - Data Explorer

Here’s a more general formula to get the nth. field from a Rollup/ Array/ Multi-Select field in Airatble (e.g. first/ second/ third/ …)

To get the 1.

REGEX_EXTRACT(ARRAYJOIN({values}), "^([^,]*)")

To get the 2.

REGEX_EXTRACT(ARRAYJOIN({values}), "(?:[^,]+,){1}([^,]*)")

To get the 3.

REGEX_EXTRACT(ARRAYJOIN({values}), "(?:[^,]+,){2}([^,]*)")


To get the last

REGEX_EXTRACT(ARRAYJOIN({values}), "([^,]*)$")
9 - Sun
9 - Sun

Yeah, it kind of makes sense (from a programmer’s perspective) for the first item to be index 0; then -1 is the one right “before” that (in an imaginary wheel), which of course is the LAST item. But for most folks working in Airtable, your suggestion of the first item being index 1 is, I think, appropriate, even if it is a bit like the Julio-Claudian calendar, which has no year 0. :slightly_smiling_face: