topic Re: Modify (aka Mass Impact) Formulas and RegEx Examples in DemandTools

Modify (aka Mass Impact) Formulas and RegEx Examples

michael-wtds — Thu, 31 Aug 2023 12:47:29 GMT

@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

Re: Modify (aka Mass Impact) Formulas and RegEx Examples

AnthonyValidity — Thu, 31 Aug 2023 12:55:47 GMT

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,

Re: Modify (aka Mass Impact) Formulas and RegEx Examples

Ashley_Validity — Thu, 31 Aug 2023 13:49:37 GMT

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

Re: Modify (aka Mass Impact) Formulas and RegEx Examples

michael-wtds — Thu, 31 Aug 2023 14:16:11 GMT

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

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

Re: Modify (aka Mass Impact) Formulas and RegEx Examples

AnthonyValidity — Thu, 31 Aug 2023 14:38:28 GMT

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,

Re: Modify (aka Mass Impact) Formulas and RegEx Examples

michael-wtds — Wed, 03 Jan 2024 02:59:20 GMT

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",

""))))

Re: Modify (aka Mass Impact) Formulas and RegEx Examples

Ashley_Validity — Wed, 10 Jan 2024 01:19:05 GMT

This is great, thanks @michael-wtds!