Modify (aka Mass Impact) Formulas and RegEx Examples

[michael-wtds]

@AnthonyValidity following yesterday call when regex formulas came up, I thought it would be useful to share a compilation of regex formulas for Modify and Matching mapping comparison

Disclaimer: Many of these formulas were sourced from online platforms like Validity, other Trailhead, Stack Exchange and other websites. While not all are my original work, they have been modified to suit my specific needs.

1. Update Account Territory by Billing State
if_StringReturn( isNull({billingstate}), "Unassigned",
if_StringReturn( RegExReplace({billingstate},"IA|IL|IN|KS|MI|MN|MO|ND|NE|OH|OK|SD|TX|WI","1")="1","Central",
if_StringReturn( RegExReplace({billingstate},"AL|AR|CT|DC|DE|FL|GA|KY|LA|MA|MD|ME|MS|NC|NH|NJ|NY|PA|RI|SC|TN|VA|VT|WV","1")="1", "East",
if_StringReturn( RegExReplace({billingstate},"AK|HI|AZ|CA|CO|ID|NM|NV|OR|UT|WA|WY|MT","1")="1", "West",
"Unassigned"
))))

2. NA Phone Fix
IF(OR( CountryMatchReturnLongName(${MailingCountry}, true) = "United States",
OR(LEFT(${Phone},1) = "1", LEFT(${Phone},2) = "+1"),
AND(LEN(${MailingCountry}) = 0, LEN(${MailingState}) = 0)),
NaPhoneFix(${Phone}),
${Phone})

3. Extract domain from website field:
RegExReplace(${Website}, "^(?:https?:\/\/)?(?:www\.)?([^\/]+)(\/.*)?$", "$1")

4. Extract domain from email:
RegExReplace(${Email}, ".*@(.+)$", "$1")

5. Matching: Last Name from a full name source field
Regular Expression: (\b(\w+)\W*$)

6. Matching: Full name without any whitespace
Regular Expression: (\s\w+)

7. Remove any special character from a name field
RegExReplace(${Name}, "[\!\@\#\$\%\^\&\*\(\)\_\-\+\=\.\;\:\?\<\,\>\{\}\[\]\`\~]", "")

8. Match the right 8 digits of account number
- useful when file drops leading zeros for a text field that always has the leading zeros
<MatchField>salesconnect__customer_account_number__c</MatchField>
<MappingType>Regular Expression: (\d{8}$)</MappingType>

Feel free to add your own formulas or modifications to this list.

All the best!
Michael

[michael-wtds · Accepted solution]

I see @Ashley_Validity has published some formulas in a prior post as well - 

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

[AnthonyValidity]

Hey @michael-wtds,

This is great stuff!  I really appreciate you posting this year for all to use.  I'm going to look them over and "borrow" some myself 🙂

Regards,

[Ashley_Validity]

Wow, this is awesome @michael-wtds Thanks for sharing! 

[michael-wtds · Accepted solution]

I see @Ashley_Validity has published some formulas in a prior post as well - 

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

[AnthonyValidity]

Hi @michael-wtds,

Here's one we discussed yesterday.  It is a way to replace 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")

Regards,

[michael-wtds · Accepted solution]

Here's an updated formula that works with DTV  - 

1. Update Account Territory by Billing State

IF( LEN(${BillingState}) <> 2, "",

IF( RegExReplace(${BillingState}, "(CT|DE|MA|ME|NH|NJ|NY|PA|RI|VT|IA|IL|IN|MI|MN|ND|NE|OH|SD|WI|WV)","1") = "1", "North",

IF( RegExReplace(${BillingState}, "(AK|AZ|CA|HI|ID|MT|NM|NV|OR|UT|WA|WY)","1") = "1", "West",

IF( RegExReplace(${BillingState}, "(AR|CO|KS|LA|MO|OK|TX|AL|DC|FL|GA|KY|MD|MS|NC|SC|TN|VA)","1") = "1", "South",

""))))

[Ashley_Validity]

This is great, thanks @michael-wtds!