Introduction

Formula Fields in Salesforce can use the Text Return Type to concatenate strings, display texts or images conditionally, and even convert a number or date value to a text value.

Text values are easy to understand and are the most convenient way to display information in your org.

Example:

Let’s create a Formula Field with Text Return Type to display the Salutation, First Name, and Last Name of Contacts in our org as a single string. We’ll use the Concatenate (&) operator to connect the strings and insert a blank space between the strings to ensure that the field displays as Mrs. Anna Jones instead of Mrs.AnnaJones.

TEXT(Salutation)   & " " &  FirstName & " " &  LastName

Common Text Functions and Operators

Let’s take a look at some of the handy Text functions and operators we can use to our advantage.

Text Operators

Concatenate (& and +) Operator

We can use the concatenate operator to join one or more strings.

Example:

1. Full Name
The example we worked on in the introductions does just that! We used the concatenate operator to connect the fields, Salutation, First Name, and Last Name, and display them as a single string.

TEXT(Salutation)   & " " &  FirstName & " " &  LastName

2. Assistant Details
Let’s display the Fields, Assistant And Asst. Phone, as a single string in the Contact Record Details page using the Concatenate operator.

AssistantName + " - " +  AssistantPhone

Text Functions

Let’s take a look at some of the frequently used text functions (for a better view, right-click on the image and click on “Open image in new tab”):

Use Text Return Type in Formulas

Assign an Account Rating

The Account Rating can take on three values → Hot, Warm or Cold.

Hot – If the Account has an Annual Revenue greater than 500,000, the Country is the United States, and the Industry is Finance.

Warm – If the Account has an Annual Revenue greater than 500,000, the Country is the United States, and the Industry is either Construction or Telecommunications.

Cold – In all other cases.

The formula contains a series of IF() statements. It includes an AND() and an OR() function too.

We use the function CONTAINS() along with the CASE() function to determine whether the Billing Country is the United States. We use ISPICKVAL() to determine the value of the picklist field, Industry.

IF( AnnualRevenue > 500000 && 
    CONTAINS(CASE (BillingCountry,
               "United States","US",
               "USA", "US",
               "America","US",
               "US","US",
               "NA"),"US"),
     IF(ISPICKVAL(Industry,"Finance"),"Hot",
        IF(ISPICKVAL(Industry,"Telecommunications") ||
           ISPICKVAL(Industry,"Construction"),"Warm","Cold")),"Cold")

For a better understanding, let’s break down what’s happening here with the help of an example:

Account ABC

Annual Revenue: 5000000

Billing Country: US

Industry: Construction

The above example satisfies the first IF condition (Annual Revenue > 500,000 AND Country = “US”), so we’ll evaluate the second IF condition, i.e., whether the value of the field, Industry, is Finance, which returns a FALSE. So we move onto the third IF condition, we check whether the Industry is either Telecommunications OR Construction, which returns TRUE. Thus Account Rating is assigned the value “Warm.”

Display an Image with CASE()

Formula Fields with Text Return Type aren’t limited to texts. We can use them to display Images too! Images are fun and can help Users comprehend information in a single glance.

Let’s create a field based on the custom formula field, Account Rating, that we created in the last section. Depending on the rating, the field displays an image.

Hot – A Red Circle

Warm – An Orange Circle

Cold – A Blue Circle

If the Account Rating field is blank, the field shows a transparent circle.

The field contains an alternate text if the image cannot be rendered for any reason. Let’s use the function IMAGE(image_url, alternate_text, height, width) to display the specified image.

CASE( Account_Rating__c , 
"Hot”,HYPERLINK("/img/samples/red.gif","Hot"), 
"Warm”,HYPERLINK("/img/samples/orange.gif","Warm"), 
"Cold",HYPERLINK("/img/samples/blue.gif","Cold"), 
 
HYPERLINK("/img/samples/transparent.gif","Unknown"))

Common Errors with Text Formulas

• Before using the HYPERLINK function, review the differences between custom links and hyperlinks.
• When inserting text strings in your formula, always enclose the text in double quotes. For example, use “CASE:” & Account_Number to display CASE:12345434
• Be mindful when using the TEXT function to convert values to text. For example, maybe the previous admin saved a date value as a text value.