Skip to main content
Solved

Extract first option from multiselct field

  • September 8, 2021
  • 3 replies
  • 56 views
L
Forum|alt.badge.img+7

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.

Best answer by Kamille_Parks11

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}
   ), 
   '"', 
   ''
))

3 replies

Kamille_Parks11
Forum|alt.badge.img+27
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.

L
Forum|alt.badge.img+7
  • Lewis_Sternberg
  • Author
  • Known Participant
  • September 8, 2021
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:

Kamille_Parks11
Forum|alt.badge.img+27

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}
   ), 
   '"', 
   ''
))
M
L
Terms & ConditionsAccessibility statement