Formula to identify alphanumeric zip codes

[Idyllic_Data_Geek]

Hello All,

I have a very simple req and I;m relatively new to Alteryx so seeking your help. I have a requirement where I want to identify the zip codes in the data set that are less than 5 char, = 0 or are alpha numeric. I have solved the first 2 and need your help create a formula that will help me identify whenever the zip code has any alphabet... I played around with few regex calcs but I was not able to get the result I needed. Thanks in advance for your help

[mceleavey]

Hi @Idyllic_Data_Geek ,

can you provide a sample data set so we can help you?

Thanks,

M.

[Maskell_Rascal · Accepted solution]

Hi @Idyllic_Data_Geek 

You can use a regex formula to find alpha numeric matches. The below formula looks for any letter characters and returns the number of matches it finds for the given field. 

REGEX_CountMatches([Zip Code], '[A-Za-z]')

You can write an IF statement to handle the total expression like the below:

IF REGEX_CountMatches([Zip Code], '[A-Za-z]')>0 
THEN 'AlphaNumeric' 
ELSEIF Length([Zip Code])<5 
THEN 'Short' 
ELSE 'Valid' 
ENDIF

Cheers!

Phil

[Idyllic_Data_Geek]

Hi Phil...Why are we doing regex_countmatches and not just the regex_match? I earlier had the below which obviously did not solver my issue

if REGEX_Match([New Zip], ".*a-z.*") then '1'

else

'0'

endif

[Maskell_Rascal]

@Idyllic_Data_Geek  It has to do with what the formula is attempting to accomplish. REGEX_Match is trying to match the exact pattern provided, so if the pattern doesn't match then everything fails. This means that you can't look for a random Alpha character. REGEX_CountMatches finds every available match within a string, so is more flexible for your scenario. 

[Maskell_Rascal]

I should also point out that your initial REGEX_Match formula would work if you add brackets to the a-z part of it. 

if REGEX_Match([Zip Code], ".*[a-z].*") then '1'

else

'0'

endif

[Idyllic_Data_Geek]

Thank you. Can I ask you another question here? I need to create a new formula that will include the text from Input file name. For e:g if the file name is 2021 NY ABCDE EGHDH Working File then field name = 'NY jason'. The text jason will always be the same...and state code will change depending on the file being used to input

[cgoodman3]

You can just write 

REGEX_MATCH([Zip Code],”.*[a-z].*”)

in the formula and if you set the data type to Bool it will either evaluate to True or False which makes for cleaner code, or put it in a filter tool and the matches will come out the true anchor which will likely save you a tool downstream.

[cgoodman3 · Accepted solution]

If the state is always two letters with a space either side, you can use regex to parse out this pattern to get the state which can then be used to create the new field:

\s(\w{2})\s

where

\w matches any word character

{2} matches the previous token exactly 2 times

\s is any white space

[Idyllic_Data_Geek]

Thank you...How do I bring in the input file name into the regex function? The input file name will be dynamic