True/False If a Field Contains a Letter

[Dyl_A]

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!

[patrick_digan · Accepted solution]

@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.

[MarqueeCrew]

@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

[patrick_digan]

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:

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]

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