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

[Ashley_Validity]

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

Session Recording
Modify Formula Dictionary 
Wildcard Documentation/Examples 

[Ashley_Validity · Accepted solution]

Modify Formulas covered in today's session: 

Adding US Country Code to Phone Numbers

• CONCATENATE("+1", ${Phone})

Remove Special Characters from any field:

• RegExReplace(input_string, "[\!\@\#\$\%\^\&\*\(\)\_\-\+\=\.\;\:\?\<\,\>\{\}\[\]\`\~\(\)]", "")

Fix 9 Digit US Zip Codes that do not have a hyphen:

• CONCATENATE(MID(${MailingPostalCode}, 1, 5),"-", MID(${MailingPostalCode}, 6, 4))

Return US Zip Codes That are Malformed:

• RegExMatch(${BillingPostalCode}, "^[0-9]{5}(?:-[0-9]{4})?$")

Return Canada Postal Codes that are Malformed:

• RegExMatch(${BillingPostalCode}, "[ABCEGHJKLMNPRSTVXY][0-9][ABCEGHJKLMNPRSTVWXYZ] ?[0-9][ABCEGHJKLMNPRSTVWXYZ][0-9]")

Return UK Postal Codes that are Malformed:

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

[cj_malwarebytes]

Hi Ashley,

Happy Monday!

Thanks for sharing the advanced formulas.

I've found an error with the last formula to Return UK Postal Codes that are Malformed:

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

error says: There seems to be a problem with the formula syntax, please check it over and try again.

Thanks,

Carlos

[JonG_Validity]

Hi @cj_malwarebytes, I just copied and pasted into my instance and I think it's just an issue with the quotations - try removing the set of quotes surrounding the RegEx formula and insert them again. It should appear red once the update has been made. Let me know if that doesn't work and I'll take another look!

[cj_malwarebytes]

Hi Jon, It worked, thank you so much!!

[cj_malwarebytes]

Hi Jon,

would I be able to include the account ids to get in an export?

I'm trying to check the malformed postal codes from the UK, however, the NewBillingPostalCode would make it completely Blank/Null and I don't want to do that unless it is a completely incorrect billing postal code. The problem with UK is that we have billing associated to those addresses, I might not want to update those.

Thanks,

Carlos

[JonG_Validity]

Hey Carlos, absolutely - just be sure to select ID from the Fields to Show on Preview section in step one as shown below. You can take that export file, make your updates & then upload it to Import and use the ID as your key field to make the changes in SF.

[cj_malwarebytes]

Hi Jon,

Perfect, thank you again!

[BBF]

Thank You

[michael-wtds]

Hi @Ashley_Validity there are number of formulas in the Modify Function list that aren't listed on DemandTools - Modify Formula Dictionary page.

Is there another resource to find an explanation on these, or could you get the additional formula's added.

Here are few examples -
AVEDEV(number1, number2, ... )
AVERAGE(number1, number2, ...)
CEILING(number, significance)
CHOOSE(index_num, value1, value2, ...)
CLEAN(text)
CODE(text)
...
TEXT(value, format_text)

Thank you!
Michael W