In Salesforce reports you can create a nested formula using IF and CONTAIN functions to search for different text with a field value, and then create a new column.
What on earth does that mean? Let me explain.
Let’s say I had a list of UK Account Names and I wanted to identify which Accounts were public companies and which were private companies based purely on their Account Name.
I could search in the Account Names to identify which had the public and private identifiers, for example:
- plc = public
- public limited company = public
- limited = private
- ltd = private
Steps
To achieve the above requirement we could follow the steps below:
- In the report create a new Column. Select ‘Add Row-Level Formula’ – see below
- In the new window that pops up, give your new column a Column Name and Description
- Formula Output Type = Text
- In the Formula field enter:
IF(CONTAINS(ACCOUNT.NAME, “plc”),
“Public”,
IF(CONTAINS(ACCOUNT.NAME, “Plc”),
“Public”,
IF(CONTAINS(ACCOUNT.NAME, “public limited company”),
“Public”,
IF(CONTAINS(ACCOUNT.NAME, “Public Limited Company”),
“Public”,
IF(CONTAINS(ACCOUNT.NAME, “Ltd”),
“Private”,
IF(CONTAINS(ACCOUNT.NAME, “Limited”),
“Private”,
“Unknown”
)
)
)
)
)
)
Note: please remember that Salesforce’s CONTAINS function is case-sensitive, hence, plc and Plc have both been included.
- Apply the formula above.
- Once refreshed your report will look like the below:
The first company contains none of the search terms so is given the final value ‘Unkown’ from the formula.
In this example, we have included the search for ‘public limited company’ or ‘Public Limited Company’ before the search for simply ‘Limited’ or ‘limited’, this is because the IF clauses are run in the order they are presented in the formula.
The formula is simply asking Salesforce to look at each Account Name and to look for certain text phrases. If a certain text phrase is found, then the field value is generated (Public or Private). If none of the text phrases are found in the Account names, then the value of ‘Unkown’ is generated.
Please let me know if you have any thoughts on the above approach using IF and CONTAINS functions in Salesforce.
