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
Solved! Go to Solution.
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
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
@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.
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
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
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.
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
Thank you...How do I bring in the input file name into the regex function? The input file name will be dynamic