community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
#SANTALYTICS

The highly anticipated Alteryx Community tradition is back! We hope you'll join us!

Learn More

If EXACT number within a string

Hi all

 

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. 

match.png

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

Hi @LaurenGess

 

See solution with few steps attached.

FewSteps.PNG

Cheers,

Labels