Formula Examples: Using Formulas for Advanced Data Standardization

AnthonyValidity — Tue, 14 Nov 2023 17:26:57 GMT

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

Modify Formula Dictionary
Wildcard Documentation / Examples

Re: Formula Examples: Using Formulas for Advanced Data Standardization

AnthonyValidity — Tue, 14 Nov 2023 17:30:55 GMT

Modify Formulas covered in today's session:

I figured this one out!!! I had to share it first. The ability to remove all special characters from a field. For this example it is a last name so I ignored spaces and hyphens.

RegExReplace(${LastName}, "[^a-zA-Z\s-]+","")

Better Way to Add Email Field from Contact to Account Website Field

AllTextAfterChar(${Email}, "@")

Clear Out Zip Codes with Less than Five Characters. Look for Zip Codes with 9 Characters and No Hyphen, Format Those Correctly Ex: xxxxx-xxxx

IF(LEN(${MailingPostalCode})<5,ZipCodeClean(${MailingPostalCode}),IF(AND(LEN(${MailingPostalCode})=9,${MailingPostalCode}<>"-"),CONCATENATE(MID(${MailingPostalCode}, 1, 5),"-", MID(${MailingPostalCode}, 6, 4)),${MailingPostalCode}))

Keep the First Five Digits of a Zip Code

RegExMatch(${OtherPostalCode}, "\d{5}")

If the Alternate Email is the Same as the Primary Email, Clear Out the Alternate Email Field

IF(${Email}=${Alternate_Email__c}, "",${Alternate_Email__c})

Look for Keywords in a Title of a Contact and if There is a Match, Change the Department Field

IF(ISNUMBER(SEARCH("Program",${Title})),"Product Management",IF(ISNUMBER(SEARCH("SDR",${Title})),"Marketing",IF(ISNUMBER(SEARCH("",${Title})),"Unknown",${Department})))

Replace Most/All Accented Letters with Their English Letter Equivalents

SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( ${Name},"ā","a"),"à","a"),"á","a"),"â","a"),"ã","a"),"ä","a"),"å","a"),"è","e"),"é","e"),"ê","e"),"ë","e"),"ç","c"),"ì","i"),"í","i"),"î","i"),"ï","i"),"ð","th"),"ñ","n"),"ò","o"),"ó","o"),"ô","o"),"õ","o"),"ö","o"),"ù","u"),"ú","u"),"û","u"),"ü","u"),"ý","y"),"ÿ","y"),"š","s"),"Ž","z")

Validate UK Zip Codes (Don't process but export list)

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

topic Formula Examples: Using Formulas for Advanced Data Standardization in DemandTools