Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

True/False If a Field Contains a Letter

Dyl_A
5 - Atom

Hi,

 

I want to create a new column at the end of my existing input data that determines if a field containing numeric values has a letter somewhere in the value. Is it possible to have this new column populated by true/false values? True, if the existing numeric value contains a letter somewhere in it. False if the existing numeric value does not contain a letter somewhere in it.

 

The field is an identification number. Not sure why some of them contains numbers and letters but they shouldn't.

 

Thank you!

5 REPLIES 5
patrick_digan
17 - Castor
17 - Castor

@Dyl_A You could use a formula tool to define a new field with this sort of formula:

 

IIF(REGEX_CountMatches([Field1],"[[:alpha:]]")>0,"True","False")

That seemed to work on my end.

Dyl_A
5 - Atom

 

 

MarqueeCrew
20 - Arcturus
20 - Arcturus

@patrick_digan solves this one, but @MarqueeCrew chimes in with:

 

!regex_Match([field1],"\d+")

Explanation:

! = NOT

regex_Match([Variable Name],"\d+") is looking for the presence of 1 or more numbers.  This will naturally return TRUE if it is all numbers.

 

By applying the ! (NOT) in front of the function it is reversed and will return FALSE if it has ONLY numbers.

 

I caution you that both of our solutions are faulty.  If you are looking for a field to contain both numbers and letters only (no spaces or special characters)...

 

REGEX_Match([Field1], "[a-zA-Z0-9]+") AND
!REGEX_Match([Field1], "[0-9]+")

That expression checks to see if the contents are only alphanumerics (same as original solution) but also checks to see that it isn't all numbers.

 

Cheers,

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
patrick_digan
17 - Castor
17 - Castor

Sure! Glad to see @MarqueeCrew jumping in as well!

 

IIF(expression,"True","False")

First I used the IIF function to check if an expression is true or false, If it's true, it will output the first value (in this case "True", but it could have been anything:"tomato" or 127 or [Field]). The second value (the string "False" in this case) will output if the expression is false. Alteryx also supports IF...Then...Else...Endif formulas as well.

 

REGEX_CountMatches([Field1],"[[:alpha:]]")>0

the expression I've used is the Regex_ContMatches function. It's checking the field [Field1] and returns how many times it comes across the pattern "[[:alpha:]]". My Regex knowledge is admittedly small, but I know where to get the info I need. I usually drop a regex tool onto the canvas and it has a wealth of info:

Capture.PNG

 

That's where I got the pattern of [[:alpha:]]. Notice that the bottom of that list has a link to complete syntax which is very helpful for Regex.

 

So the regex_countmatches will return the number of letters. If that number is >0, then my IIF will return true.

MarqueeCrew
20 - Arcturus
20 - Arcturus

Ding Dong!

 

One more chime:

 

REGEX_Match([Field1], "[a-zA-Z0-9]+") AND
!REGEX_Match([Field1], "[0-9]+") AND
!REGEX_Match([Field1], "[a-zA-Z]+")

now it is truly #s and Letters.  That answer requires it to be letters and numbers to be TRUE.

 

Cheers,

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
Labels