Question - Data Validation

[reginawhelan]

I've created a flow that's validates and cleanses manually inputted (Excel Spreadsheet) data so it can load into our system without and issues. 

I am stuck on the postal code. Since the data is manually inputted there are some with missing characters or invalid sequences. Our system requires the A1A 1A1 alphanumeric sequence. 

Is there a formula that would report on invalid sequences, for example: 1A1 A1A, A1A 1A, etc...?

[AlteryxUserFL]

This should be moved to the Designer Discussion,  but this workflow should solve your problem. It will compare input postal codes to all postal codes to check for a match. 

[reginawhelan]

Thank you. We do multiple loads every day and this list will eventually be outdated. Do you know of a formula that would check the format of the postal code?

[AlteryxUserFL · Accepted solution]

Try putting this into a formula tool and let me know if it works. 

Update: Add one more check at the end for non white space special symbols.  

IF Length([ZIP]) = 7 and  !regex_Match(Substring([ZIP],0,1),"\d+") and  regex_Match(Substring([ZIP],1,1),"\d+")  and  !regex_Match(Substring([ZIP],2,1),"\d+") and  regex_Match(Substring([ZIP],4,1),"\d+") and  !regex_Match(Substring([ZIP],5,1),"\d+")and  regex_Match(Substring([ZIP],6,1),"\d+") and Substring([ZIP],3,1) = " " and if REGEX_Match([ZIp], "[\w+|\s]+") then "TRUE" else "FALSE" endif 


then "Match" else "Not Match" endif 

[reginawhelan]

Thank you! Works perfectly.