Validity Customer Community

Ashley_Validity · ‎07-11-2023

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

Ashley_Validity · ‎07-11-2023

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

AnthonyValidity · ‎12-29-2023

@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

Validity Customer Community

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

Get your blog on