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...?
Solved! Go to Solution.
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?
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
Thank you! Works perfectly.