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.
Sep 12, 2023 12:28 AM
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:
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.
Solved! Go to Solution.
Sep 13, 2023 12:01 AM
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
)
Sep 12, 2023 05:25 AM
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?
Sep 12, 2023 05:38 AM
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"'
Sep 12, 2023 04:06 PM
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
)
Sep 12, 2023 10:47 PM
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.
Sep 13, 2023 12:01 AM
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
)
Sep 13, 2023 12:11 AM - edited Sep 13, 2023 12:12 AM
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
)
Sep 13, 2023 12:20 AM
If you remove the LEN, it will contain "c" and "a,b" and will match any other choice.
OK if not a problem!
Sep 13, 2023 02:34 AM
@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.