ARRAYSLICE is not working with lookup, linked records, or multi-select fields

[automator]

I have been reading for hours through the help forum and documentation, but ARRAYSLICE doesn't want to work for me.

I want to achieve the following: Show me the first value of an array.

My formula is:

ARRAYSLICE(verticals_from_contact, 1, 1)

but it doesn't show me the first value of the array. Instead, the field shows me the whole array. If I change it to:

ARRAYSLICE(verticals_from_contact, 2, 2)

nothing is shown anymore. 

I tried the formula on lookup fields, linked records, and multi-select fields, and nothing worked (see image). Does anyone have an idea what's wrong?

[automator]

For anyone who will read this in the future, I just made the following learning:

According to the Airtable documentation "Array functions can only be used in rollup fields or when the input field is a lookup field."

This is misleading and not 100% correct. The lookup field's underlying field is a multi-select type, and ARRAYSLICE doesn't seem to work with this type.

ARRAYSLICE does work with text fields of lookup fields (e.g. name, etc.), and not other field types.

[automator · Accepted solution]

I found the solution, and I hope it helps everyone who faces the same issue.

We need to extract the values with REGEX. The formulas to extract the first, second, and last values are as follows:

Extract first value:

REGEX_EXTRACT(verticals_from_contact,"[^,]*")

Extract second value:

REGEX_EXTRACT(verticals_from_contact,"(?:, )([^,]*)")​

Extract the third value:

REGEX_EXTRACT(verticals_from_contact,"(?:[^,]*,){2}([^,]*)")​

Extract the last value:

REGEX_EXTRACT(verticals_from_contact,"[^,]*$")​

I have no idea how this works, but after endless trial and error, it extracts the correct values for me.