This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
Gather all 9 clues to complete the final Weekly Challenge on Dec 16!
I have a field that is a string that I need to identify if an exact number is included within it by marking a 1 else 0.
My goal is to mark 1 if the field has exactly a 15 (and a short list of other numbers like exactly a 39, 197, 210, etc.) within the string, and all else mark 0)
Small sample of the string field is below - with desired results :
15 - 1
18015,N519 - 0
256,15 - 1
26,150 - 0
242,15,204 - 1
B15,97,234,N130 - 0
N517,N356,M119,96,185,15 - 1
36,15,192,15 - 1
N115,96,222,N59 - 0
N522,N519,M15,B15,50,2,151 - 0
The 15 could be anywhere within the string (beginning, middle, or end) or even listed multiple times in which I'd still want to mark 1.
I have tried Contains and In functions - neither producing the results I am looking for.
IF Contains([FIELD], "15") THEN 1 ELSE 0 ENDIF
This gives me everything with a 15, including 150, B15, N115, 18015 etc. (I do not want these)
IF Contains([FIELD], "15") OR Contains([FIELD], ",15") OR Contains([FIELD], "15,") THEN 1 ELSE 0 ENDIF
This get me closer, but gives me ,151 and ,159 and M15, etc. (I do not want these)
IF [FIELD] IN ("15") THEN 1 ELSE 0 ENDIF
This only gives me 15 and not 256,15 or 242,15,204 etc. (I want these)
Please help! Thanks!
I can think of two options that would probably work.
1. RegEx, which I'm not great at, but could work
2. The attached work flow does what you're looking for, but if you have a massive dataset, it may not be best option.
See solution with few steps attached.