Help

Exact match formula for Multiple select field when value has comma

Topic Labels: Formulas
Solved
Jump to Solution
1166 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?

 

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
)

 

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!

@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.