Hi,
I’m trying to create a formula that flags (with a 1 or 0) when a certain 5 digit number code is present in either a linked record field, or a field of arrays (whatever is easiest).
For example, I was attempting:
``IF(
OR(
FIND(‘68100’,{Combined Codes & Tags (all)}),
FIND(‘41100’,{Combined Codes & Tags (all)}),
),
1,0)``
But this only works when I have two possible options of codes. I’m actually looking for the presence of 5 different codes. What is the best way to do this?
Do you need to know if more than one of those codes is present?
If there’s only 5 codes, and you only need to know whether one of them is present, it seems it would be reasonable to expand the formula you already wrote to include all 5.
If you want to know how many of those codes are present, you’ll want to take extra steps. Maybe something like this…
IF(FIND("68100", {Combined Codes & Tags (all)}), 1, 0) & IF(FIND("41100", {Combined Codes & Tags (all)}), 1, 0) & IF(FIND("Code3", {Combined Codes & Tags (all)}), 1, 0) & IF(FIND("Code4", {Combined Codes & Tags (all)}), 1, 0) & IF(FIND("Code5", {Combined Codes & Tags (all)}), 1, 0)
Say that field is named {Codes}, and gives us a result of “10111” - you could then use a formula like this…
SUM(VALUE(LEFT(Codes, 1)), VALUE(MID(Codes, 2, 1)), VALUE(MID(Codes, 3, 1)), VALUE(MID(Codes, 4, 1)), VALUE(RIGHT(Codes, 1)))
Which would give us a result of 4 codes present. Hope that helps!
+21To expand upon the solution by @AlliAlosa, you could tweak the formula to only return a 1 if a given code is found, not even bothering with a 0 if it’s not found. That way you’d only have a series of 1’s in the end, and you can wrap a LEN() around that resulting string to count them. The whole modified formula would look like this:
LEN(IF(FIND("68100", {Combined Codes & Tags (all)}), 1) &
IF(FIND("41100", {Combined Codes & Tags (all)}), 1) &
IF(FIND("Code3", {Combined Codes & Tags (all)}), 1) &
IF(FIND("Code4", {Combined Codes & Tags (all)}), 1) &
IF(FIND("Code5", {Combined Codes & Tags (all)}), 1))
No account yet? Create an account
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.