Follow up to "Modify: Using Advanced Formulas for Data Standardization" Webinar

[AnthonyValidity]

Hi Everyone,

It was a pleasure hosting the Modify: Using Advanced Formulas for Data Standardization webinar today.  I hope you all found it useful!

I'm going to include all formulas that I used as examples during the session.  I will also provide the list of formulas available shortly.

Best,

[AnthonyValidity]

Contact Object:

Changing CurrencyIsoCoe (If in US, change to USD):
IF(OR(${MailingCountry}="US",${MailingCountry}="USA",${MailingCountry}="United States"), "USD", ${CurrencyIsoCode})

Changing Alternate_Email__c  (If same as Email, clear out):
if(${Email}=${Alternate_Email__c}, "",${Alternate_Email__c})

Changing FirstName (Can be used on any field, replaces accented letters with English letters):
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( ${FirstName},"ā","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")

Changing Department (Searches for keywords):
if(isnumber(search("Program",${Title})),"Product Management",if(isnumber(search("SDR",${Title})),"Marketing",if(isnumber(search("",${Title})),"Unknown",${Department})))

Changing MailingPostalCode :
IF(LEN(${MailingPostalCode}) < 5,ZipCodeClean(${MailingPostalCode}),IF(AND(LEN(${MailingPostalCode}) = 9,NOT(ISNUMBER(SEARCH("-",${MailingPostalCode})))),CONCATENATE(MID(${MailingPostalCode}, 1, 5),"-",MID(${MailingPostalCode}, 6, 4)),${MailingPostalCode}))

Lead Object:

Changing City (Removing special characters):
RegExReplace(${City}, "[^a-zA-Z\s.]", "")

Changing Invalid_Email_Syntax__c (simple email syntax checker):
IF(ISBLANK(${Email}),"",IF(RegExMatch(${Email}, "^[a-zA-Z0-9_.-]+@[a-zA-Z0-9-]+(\.[a-zA-Z0-9-]+)*$")="",TRUE,FALSE))

Changing Preferred_Communication__c (Moving from many values to just three):
IF(OR(AND(${Preferred_Communication__c}="Email",${Invalid_Email_Syntax__c}=FALSE),${Preferred_Communication__c}="Phone", ${Preferred_Communication__c}="Snail Mail"),${Preferred_Communication__c},IF(ISBLANK(${Email}),IF(ISBLANK(${Phone}),"","Phone"),"Email"))

Changing LastName:
RegExReplace(${LastName}, "[^a-zA-Z\s\-]", "")

[AnthonyValidity]

Spreadsheet Formulas Available in DTV's Modify Module:

Function
ABS
AND
AVEDEV
AVERAGE
CEILING
CHAR
CHOOSE
CLEAN
CODE
COMBIN
COMPLEX
CONCATENATE
COUNT
COUNTA
DATE
DAY
DAYS360
DEGREES
DELTA
DEVSQ
DOLLAR
EDATE
EOMONTH
EVEN
EXACT
EXP
FACT
FACTDOUBLE
FIND
FIXED
FLOOR
FV
HOUR
IF
IFERROR
IPMT
IRR
ISBLANK
ISERR
ISERROR
ISEVEN
ISLOGICAL
ISNA
ISNONTEXT
ISNUMBER
ISODD
ISREF
ISTEXT
LARGE
LEFT
LEN
LN
LOG
LOG10
LOWER
MATCH
MAX
MAXA
MEDIAN
MID
MIN
MINA
MINUTE
MIRR
MOD
MODE
MONTH
MROUND
NETWORKDAYS
NOT
NOW
NPER
NPV
ODD
OR
PERCENTILE
PI
PMT
POWER
PPMT
PRODUCT
PROPER
PV
QUOTIENT
RAND
RANDBETWEEN
RANK
RATE
REPLACE
REPT
RIGHT
ROMAN
ROUND
ROUNDDOWN
ROUNDUP
SEARCH
SECOND
SIGN
SLOPE
SMALL
SQRT
STDEV
SUBSTITUTE
SUM
SUMPRODUCT
SUMSQ
SUMX2MY2
SUMX2PY2
SUMXMY2
TEXT
TIME
TODAY
TRIM
TRUNC
VALUE
VAR
VARP
WEEKDAY
WEEKNUM
YEAR
YEARFRAC
FALSE
TRUE

[AnthonyValidity]

DTV's Formula Dictionary Article

AllTextAfterChar
AllTextBeforeChar
ConvertData
ConvertToString
CountryMatchReturn2CharacterName
CountryMatchReturn3CharacterName
CountryMatchReturnISONumber
CountryMatchReturnLongName
DateAddValue
FirstWord
IntlPhoneFix
Mid_1
Mid_2
New_Line
Normalized_US_Address
Normalized_US_Address2LineSuiteFirst
Normalized_US_Address2LineSuiteLast
ParseNameReturnFirstName
ParseNameReturnLastName
ParseNameReturnSuffix
ParseNameReturnTitle
ProperCaseName
ProperCaseNameForce
RegExMatch
RegExReplace
RelaxedDomain
RemoveLastWord
StateMatchLongName
StateMatchShortName
StringReplace
StringReplaceCS
TrimEndSpaces
TrimStartSpaces
ZipCodeClean