Skip to main content

I have the following options in a multiselect field with:


Table sample options:

first row: “$5,400” & “10,400”

second row: “10,400” & “Promo”


How can I extract the first option; for first row would be “$5,400” and for second row should be “$10,400”


Thank fo your help.


The ‘comma’ is giving me hard time to extract the right record.

SUBSTITUTE(
IF(
FIND(", ", {Multiselect Field}) > 0,
LEFT({Multiselect Field}, FIND(", ", {Multiselect Field}) - 2),
{Multiselect Field}
),
'"',
''
)

^ check to see if there is more than one selection, get the first selection by finding where the first comma+space there is, then remove any extraneous quotation marks inserted.


SUBSTITUTE(
IF(
FIND(", ", {Multiselect Field}) > 0,
LEFT({Multiselect Field}, FIND(", ", {Multiselect Field}) - 2),
{Multiselect Field}
),
'"',
''
)

^ check to see if there is more than one selection, get the first selection by finding where the first comma+space there is, then remove any extraneous quotation marks inserted.



Thank you very much. It does work perfect for the explained needs.


However, there is row with options: “$900” & “promo”

the output should be “$900” but it reads “$90”


I am thinking there is no magic command like EXTRACTFIRSTOPTION() :grinning_face_with_sweat:



Thank you very much. It does work perfect for the explained needs.


However, there is row with options: “$900” & “promo”

the output should be “$900” but it reads “$90”


I am thinking there is no magic command like EXTRACTFIRSTOPTION() :grinning_face_with_sweat:


Try:


TRIM(SUBSTITUTE(
IF(
FIND(", ", {Multiselect Field}) > 0,
LEFT({Multiselect Field}, FIND(", ", {Multiselect Field}) - 1),
{Multiselect Field}
),
'"',
''
))

Reply