cancel
Showing results for 
Search instead for 
Did you mean: 

Training Recording & Formula Examples: Using Formulas for Advanced Data Standardization

Ashley_Validity
Administrator
Administrator

Huge thanks to everyone who attended today's training on Using Formulas for Advanced Data Standardization.  Below is a link to the recording as well as a few additional resources that were referenced during the training.  Also, I am going to add a comment with samples of the formulas that @JonG_Validity reviewed during the session.  Feel free to drop questions below if you have them!

Session Recording
Modify Formula Dictionary 
Wildcard Documentation/Examples 

1 ACCEPTED SOLUTION

Ashley_Validity
Administrator
Administrator

Modify Formulas covered in today's session: 

Adding US Country Code to Phone Numbers

  • CONCATENATE("+1", ${Phone})

Remove Special Characters from any field:

  • RegExReplace(input_string, "[\!\@\#\$\%\^\&\*\(\)\_\-\+\=\.\;\:\?\<\,\>\{\}\[\]\`\~\(\)]", "")

Fix 9 Digit US Zip Codes that do not have a hyphen:

  • CONCATENATE(MID(${MailingPostalCode}, 1, 5),"-", MID(${MailingPostalCode}, 6, 4))

Return US Zip Codes That are Malformed:

  • RegExMatch(${BillingPostalCode}, "^[0-9]{5}(?:-[0-9]{4})?$")

Return Canada Postal Codes that are Malformed:

  • RegExMatch(${BillingPostalCode}, "[ABCEGHJKLMNPRSTVXY][0-9][ABCEGHJKLMNPRSTVWXYZ] ?[0-9][ABCEGHJKLMNPRSTVWXYZ][0-9]")

Return UK Postal Codes that are Malformed:

  • RegExMatch(${BillingPostalCode},”^[A-Z]{1,2}[0-9][A-Z0-9]? ?[0-9][A-Z]{2}$”)

View solution in original post

10 REPLIES 10

AnthonyValidity
Validity Team Member
Validity Team Member

@michael-wtds ,

Any formula in all caps should be easily found via google.  They are spreadsheet formulas that you can find in other programs like Google Sheets and Excel.

Regards,

Anthony Lardiere Jr
Senior Customer Success Manager