Formulas
Use formulas to dynamically add columns to your source data. Use common Excel & Salesforce formulas such as LEFT, CONCATENATE, and IF
To reference a source column in a formula then place the column name in square brackets
💡 [Name]
To add a static value e.g. a record type id, place the value in double quotes
💡 "0013t00001XJ2dXAAT"
Combine two or more fields by referencing their columns names and adding a + in between them.
💡 [Name] + "-" + [Type]
E.g. if the Name value is ‘Sam Hoult’, and the Type value is ‘Prospect’ then the evaluated formula for that record would be “Sam Hoult-Prospect”
Take the left n characters of a field
💡 LEFT([Name], 3)
E.g. if the Name value is Benjamin Button, then the evaluated formula for that record would be “Ben”
Take the left n characters of a field
💡 RIGHT([Name], 3)
E.g. if the Name value is Benjamin Button, then the evaluated formula for that record would be “ton”
These expressions can be combined together e.g. for a column [Date] where the values are in yyyy-mm-dd format the following formula could be used
💡 LEFT(RIGHT[Date], 5), 2)
This first takes the right 5 characters from the date string: ‘mm-dd’, and then takes the left 2 characters of that substring: ‘mm’
Replace characters in a string using either direct replacement, or a regex expression.
💡 [Email].str.replace(" ", "")💡 [Phone].str.replace("\d+", "", regex=True)
Split up a field by a specified character, and the specified number of the split. 0 gives the first value in the split, 1 gives the second and so on.
The value “[email protected]” would give the value “dataimporter.io” using the formula below.
💡 SPLIT([Email], "@", 1)
The value "One;Two;Three" would give the value "One" using the formula below.
💡 SPLIT([Picklist], ";", 0)
Convert the text of a column into lowercase characters.
"Sam Hoult" would become "sam hoult"
💡 LOWER([NAME])
True if the operands are equal. String comparisons that use the equals operator are case-sensitive.
💡 [State] == "NY"
True if the operands are not equal. String comparisons that use the not equals operator are case-sensitive.
💡 [State] != "NY"
True if the left operand is less than the right operand.
💡 [Revenue].astype('int') < 50000
True if the left operand is greater than the right operand.
💡 [Revenue].astype('int') > 50000
Determines if expressions are true or false. Returns a given value if true and another value if false.
💡 IF([State] == "NY", "New York", "Other")💡 IF([Revenue].astype('int') > 100000, "Big Customer", "Regular Customer")
You can also nest IF statements like so
💡 IF([State] == "NY", IF([Type] == "Big Customer", "NY Big Customer", "NY Regular Customer"), "Other")
Combine multiple logic statements together using the ampersand symbol. Each condition must be surrounded by parenthesis, as shown in the example below. Only when every condition returns True will the entire statement return True.
💡 IF(([State] == "NY") & ([City] == "New York City"), "Target Customer", "Other")
Combine multiple logic statements together using the ampersand symbol. Each condition must be surrounded by parenthesis, as shown in the example below. When one of the conditions returns True will the entire statement return True.
💡 IF([State] == "NY") | ([State] == "TX"), "Target Customer", "Other")
You can combine multiple & and | operators to create more complex expressions
💡 IF((([Support Plan] == "TRUE") & ([Name] == "Big Account")) | ([Big Account] == "True"), "Gold", "Silver")
Returns True is a value is Null and False if it contains a value. Will return False if the value is an empty string e.g. “”.
💡 IF(ISNULL([Name]), "Unknown", [Name])
Returns True is a value is Not Null and False if it is null.
💡 NOTNULL([Name])
Returns True if a value contains the string provided, otherwise will return False. The string provided is case sensitive.
💡 IF(CONTAINS([Name], "New"), "New Account", "Old Account")
If the value is “New York” this would return true, where as “new york” would return False.
Evaluates to the current day
💡 today()
Evaluates to the previous day
💡 yesterday()
Evaluates to the next day
💡 tomorrow()
Last modified 3mo ago