Split a List and get always the first element of the list

[rafaelpazsud]

I have a column that receives values from an enumlist like this:

INCOME_HH | min. value: 0 max. value: 0 , POLITICAL_PARTY | min. value: 0 max. value: 0 , ETHNICITY_JEWISH | min. value: 0 max. value: 1, ETC....

I wanna to SPLIT the values of a list by "|" and get a list of the first elements. for example:

INCOME_HH, ETHNICITY_JEWISH etc...

I'm using split, however, only able to get INCOME_HH as it's the first element

TEXT(
INDEX(
SPLIT(TEXT([Columns]),"|"),1
)
)

Anyone would be able to help?

cheers

[Aurelien]

Hi @rafaelpazsud 

You may want to use an apps script function to grab the desired elements.

I just asked Gemini to give me a possible function for that:

function extractFirstElements(input) {
  const items = input.split(',');
  const firstElements = [];

  for (const item of items) {
    const parts = item.split('|');
    firstElements.push(parts[0].trim());
  }

  return firstElements;
}

  It can be improved, but you can use it.

Basically, use a bot with an "on add" event, that will process this function in a step "task: call a script"

For reference:

Call Apps Script from an automation - AppSheet Help

[Suvrutt_Gurjar]

Or alternatively, within AppSheet you may want to try the following longish expression

SPLIT(SUBSTITUTE(SUBSTITUTE((SUBSTITUTE([List_Column],"| ",", @")),"min. value: ","")," max. value: ","")," , ")-

EXTRACTMENTIONS(SUBSTITUTE(SUBSTITUTE((SUBSTITUTE([List_Column],"| ",", @")),"min. value: ","")," max. value: ",""))

The expression assumes the [List_column] data will never have the "@" character.