08-31-2023 05:47 AM
@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
Solved! Go to Solution.
08-31-2023 07:16 AM
I see @Ashley_Validity has published some formulas in a prior post as well -
Training Recording & Formula Examples: Using Formulas for Advanced Data Standardization
01-02-2024 06:59 PM
Here's an updated formula that works with DTV -
1. Update Account Territory by Billing State
08-31-2023 05:55 AM
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,
08-31-2023 06:49 AM
Wow, this is awesome @michael-wtds Thanks for sharing!
08-31-2023 07:16 AM
I see @Ashley_Validity has published some formulas in a prior post as well -
Training Recording & Formula Examples: Using Formulas for Advanced Data Standardization
08-31-2023 07:38 AM
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,
01-02-2024 06:59 PM
Here's an updated formula that works with DTV -
1. Update Account Territory by Billing State
01-09-2024 05:19 PM
This is great, thanks @michael-wtds!
Get industry news, expert insights, strategies, and hot tips, served up fresh every week.
Visit the Validity blog