Help

The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.

Exact match formula for Multiple select field when value has comma

Topic Labels: Formulas
Solved
Jump to Solution
2904 8
cancel
Showing results for 
Search instead for 
Did you mean: 
Samvel_Siradegh
5 - Automation Enthusiast
5 - Automation Enthusiast

I need a formula to check if there is exact matching for multiple select field when values can have comma. Here is a sample multiple select I can have:

  • a
  • b
  • c
  • a,b

I need to check if users selection is exactly 'c' and 'a,b'. All solutions I found (like using FIND) are not working with comma.

1 Solution

Accepted Solutions
Sho
11 - Venus
11 - Venus

Ah, I see why the FIND function is not working.
Multi-select is difficult to process because it's just text.

How about this formula?

 

IF(
  AND(
    REGEX_MATCH("##"&REGEX_REPLACE({select},", ","##")&"##","##c##"),
    REGEX_MATCH("##"&REGEX_REPLACE({select},", ","##")&"##",'##"a,b"##'),
    LEN({select})=LEN('c, "a,b"')
  )
  ,1
)

 

 

See Solution in Thread

8 Replies 8
Sho
11 - Venus
11 - Venus

 

How about this formula.

IF(
  AND(
    FIND("a,b",{select}),
    FIND("c",{select}),
    LEN({select})=LEN('"a,b", c')
  )
  ,1
)

also this formula

IF(
  OR(
    '"a,b", c'={select},
    'c, "a,b"'={select}
  )
  ,1
)

Hmmm, Is there any better way?

 

Samvel_Siradegh
5 - Automation Enthusiast
5 - Automation Enthusiast

Thanks for response @Sho , but this will not work, as in my case 2 of 4 items can be selected (or 1, or 3, or all 4) and 

LEN({select})=LEN('"a,b", c')

will not be true, as for this sample {select} is 'a,b,c,"a,b"'

Sho
11 - Venus
11 - Venus

Did you try it?

"LEN()" part checks to see if any other options have been selected.

In the case of 'a,b,c, "a,b"'

IF(
  AND(
    FIND("a",{select}),
    FIND("b",{select}),
    FIND("c",{select}),
    FIND("a,b",{select}),
    LEN({select})=LEN('a, b, c, "a,b"')
  )
  ,1
)

 

Samvel_Siradegh
5 - Automation Enthusiast
5 - Automation Enthusiast

Of course I tried it.
Let me explain more visual way.
This is my multiple select:

Samvel_Siradegh_0-1694583996227.png

You can see there are 4 options.
This is selection I want to check:

Samvel_Siradegh_1-1694584039419.png

Hope this is more accurate description.

Sho
11 - Venus
11 - Venus

Ah, I see why the FIND function is not working.
Multi-select is difficult to process because it's just text.

How about this formula?

 

IF(
  AND(
    REGEX_MATCH("##"&REGEX_REPLACE({select},", ","##")&"##","##c##"),
    REGEX_MATCH("##"&REGEX_REPLACE({select},", ","##")&"##",'##"a,b"##'),
    LEN({select})=LEN('c, "a,b"')
  )
  ,1
)

 

 

Samvel_Siradegh
5 - Automation Enthusiast
5 - Automation Enthusiast

I removed LEN and looks like it works, will try some corner cases and will update here.
Here is formula I am trying

 

IF(
  AND(
    REGEX_MATCH("##"&REGEX_REPLACE({select},", ","##")&"##","##c##"),
    REGEX_MATCH("##"&REGEX_REPLACE({select},", ","##")&"##",'##"a,b"##')
  )
  ,1
)

 

Sho
11 - Venus
11 - Venus

If you remove the LEN, it will contain "c" and "a,b" and will match any other choice.
OK if not a problem!

Samvel_Siradegh
5 - Automation Enthusiast
5 - Automation Enthusiast

@Sho you are right, this is not exact matching. Your version was correct, I just had typo on my side. Will do some more tests and will accept as solution.