ARRAY_FIRST and ARRAY_LAST

[Regent_Province]

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

[Bob_Ziroll]

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

[Nicolas_d_Apri1]

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

[Alexey_Gusev]

Bob_Ziroll:

The part I still don’t understand is how taking the length differences between the string and the string without (e.g. spaces, commas, semicolons) gives you the index of the last occurance of that separator? E.g. for “Joe Bob” and “JoeBob” the length difference is 1, but the index of the space is 4, not 1. I’m sure I’m missing something basic here, but that’s been confusing for me

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]

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), "[^,]*$"))

[Kamille_Parks]

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

[Johannes_Mutter]

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]

Justin_Barrett:

FWIW, the programmer in me actually prefers zero-based indexing, though because Airtable indexes strings with a 1-based index for things like LEFT, MID, etc., I’m using that style in these samples for consistency.

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]

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.