Help

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

ARRAY_FIRST and ARRAY_LAST

cancel
Showing results for 
Search instead for 
Did you mean: 
Regent_Province
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…

Thanks,

Rémi

18 Comments
Bob_Ziroll
5 - Automation Enthusiast
5 - Automation Enthusiast

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

Nicolas_d_Apri1
5 - Automation Enthusiast
5 - Automation Enthusiast

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

Alexey_Gusev
12 - Earth
12 - Earth

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.

Justin_Barrett
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

Kamille_Parks
16 - Uranus
16 - Uranus

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

Johannes_Mutter
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}([^,]*)")


etc.

To get the last

REGEX_EXTRACT(ARRAYJOIN({values}), "([^,]*)$")
WilliamPorter
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:

Calvin_Young2
6 - Interface Innovator
6 - Interface Innovator

Some time in late 2023 they quietly added the new ARRAYSLICE() function which allows you to do this.

For example, ARRAYSLICE({Your Field}, 1, 1} will return the first value, while ARRAYSLICE({Your Field}, -1} will return the last.