+3I 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:
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.
Best answer by Sho
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("##"®EX_REPLACE({select},", ","##")&"##","##c##"),
REGEX_MATCH("##"®EX_REPLACE({select},", ","##")&"##",'##"a,b"##'),
LEN({select})=LEN('c, "a,b"')
)
,1
)
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?
+3
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"'
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
)
+3Did 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:
You can see there are 4 options.
This is selection I want to check:
Hope this is more accurate description.
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("##"®EX_REPLACE({select},", ","##")&"##","##c##"),
REGEX_MATCH("##"®EX_REPLACE({select},", ","##")&"##",'##"a,b"##'),
LEN({select})=LEN('c, "a,b"')
)
,1
)
+3Ah, 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("##"®EX_REPLACE({select},", ","##")&"##","##c##"),
REGEX_MATCH("##"®EX_REPLACE({select},", ","##")&"##",'##"a,b"##'),
LEN({select})=LEN('c, "a,b"')
)
,1
)
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("##"®EX_REPLACE({select},", ","##")&"##","##c##"),
REGEX_MATCH("##"®EX_REPLACE({select},", ","##")&"##",'##"a,b"##')
)
,1
)
If you remove the LEN, it will contain "c" and "a,b" and will match any other choice.
OK if not a problem!
No account yet? Create an account
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.