Formula Examples: Using Formulas for Advanced Data Standardization

[AnthonyValidity]

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

[AnthonyValidity · Accepted solution]

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}$")