Use Text Formulas
Introduction to Text in Formulas
Sometimes the easiest way to make information in your organization readable at a glance is to show it as text. Formulas that use the Text return type can concatenate Text values, convert numbers and dates to text, or display text conditionally.
The simplest thing you can do is concatenate two Text strings with the operator &. For example, you could display a contact’s full name by combining the first and last name.
FirstName & " " & LastName
When putting Text values together, account for necessary spaces so that your strings don’t display as nonsense—“Jane Doe” is a lot more readable than “JaneDoe.”
Common Text Functions and Operators
Salesforce comes with many functions and operators that make it easy to work with Text in formulas. Using formulas, you can convert other data types to Text and concatenate Text values together or search for a specific string within a Text field.
Converting to and from Text Values
TEXT() converts a Percent, Number, Date, Date/Time, picklist, or Currency field into Text. TEXT() returns output without any formatting, commas, or currency signs. For example, TEXT(percent_value), if percent_value is set to 30%, returns 0.3.
When TEXT() receives a Date or Date/Time value, it returns a string with standard Date or Date/Time formatting. For instance, if date_value corresponds to March 17, 2015, TEXT(date_value) returns 2015-03-17. For datetime_value March 17, 2015 at 5 PM, TEXT(datetime_value) returns 2015-03-17 17:00:00Z. The Z indicates that the time returned is in GMT— TEXT() always returns Date/Time values in GMT, not the time zone of the current user or your organization.
You can also convert values the other way using VALUE(), which takes a Text value and returns a Number. If VALUE() receives a value that is not a Number, including any special characters other than a decimal point or minus (negative) sign, it displays #Error! For example, if text_value is $500, VALUE(text_value) returns an error. VALUE() also returns an error if text_value is blank.
Other Text Functions
BEGINS(text, compare_text) returns true if text begins with compare_text. Similarly CONTAINS(text, compare_text) returns true if compare_text is anywhere in text. These functions are useful for displaying information conditionally based on a text field.
The function SUBSTITUTE(text, old_text, new_text) returns text with any instance of old_text substituted with new_text, much like the find and replace function in a text editor.
Salesforce includes other functions for working with and formatting text in formulas. For more information, see Formula Operators and Functions in Salesforce online help.
Use the Text Type in Formulas
Assign an Account Rating
Often, formulas using the Text return type display information that’s hard to quantify. You could, for example, use a Text formula to determine whether a lead is Hot, Warm, or Cold. We’ll write a formula that uses both conditional logic and the ISPICKVAL() function to return a lead rating.
For this example, we rate a lead based on its revenue, country, and source.
- Hot—AnnualRevenue is greater than $1 million, the Country is the United States, and the LeadSource is Partner Referral.
- Warm—AnnualRevenue is greater than $1 million, the Country is the United States, and the LeadSource is either Purchased List or Web.
- Cold—The account doesn’t meet any of those conditions.
This formula uses a series of IF(), AND(), and OR() statements to check these conditions and evaluates the Lead Source field using ISPICKVAL(). The formula also uses a CASE() statement to check the Country . If the country is US, USA, America, or United States, the CASE() statement returns US. Otherwise, it returns NA (not applicable).
A choice tree helps us more easily follow the logical flow of our formula.
- In Setup, use the quick find box to find the Object Manager.
- Click Lead | Fields & Relationships and click New.
- Select Formula and click Next.
- In Field Label, enter Rating. Field Name populates automatically.
- Select Text and click Next.
- Enter the following formula:
IF( AnnualRevenue > 1000000 && CONTAINS(CASE(Country, "United States", "US", "America", "US", "USA", "US", "US", "US", "NA"), "US"), IF( ISPICKVAL(LeadSource, "Partner Referral"), "Hot", IF( ISPICKVAL(LeadSource, "PurchasedList") || ISPICKVAL(LeadSource, "Web"), "Warm", "Cold" ) ), "Cold" )
The new formula field Rating displays Hot, Warm, or Cold based on a lead’s characteristics.
Display an Image with CASE()
Formulas with the Text return type are useful for more than just text—you can also use them to display images. Images make information easy to take in at a glance and catch a user’s eye more than text alone does. Let’s write a formula that uses the Rating field that you just created to display a lead’s rating graphically as zero, one, three, or five out of five stars.
The IMAGE() function takes up to four arguments. IMAGE(image_url, alternate_text, height, width) displays the image specified by image_url with the dimensions specified by the optional height and width arguments. If the image can’t be displayed, the function displays alternate_text.
If the lead Rating is
- Hot, the formula shows five stars
- Warm, the formula shows three stars
- Cold, the formula shows one star
If the lead Rating is blank, the formula shows no stars.
Create a formula field with the Text return type and the name Star Rating . In the Advanced Formula Editor, enter:
IMAGE( CASE(Rating__C, "Hot", "/img/samples/stars_500.gif", "Warm", "/img/samples/stars_300.gif", "Cold", "/img/samples/stars_100.gif", "/img/samples/stars_000.gif"), "Unknown")
This formula uses images that are built in to Salesforce, but you can use your own images by first uploading them from the Documents tab.
- This formula uses the Billing State/Province field of a U.S.-based account to classify it as North, South, East, West, or Central.
IF(ISBLANK(BillingState), "None", IF(CONTAINS("AK:AZ:CA:HA:NV:NM:OR:UT:WA", BillingState), "West", IF(CONTAINS("CO:ID:MT:KS:OK:TX:WY", BillingState), "Central", IF(CONTAINS("CT:ME:MA:NH:NY:PA:RI:VT", BillingState), "East", IF(CONTAINS("AL:AR:DC:DE:FL:GA:KY:LA:MD:MS:NC:NJ:SC:TN:VA:WV", BillingState), "South", IF(CONTAINS("IL:IN:IA:MI:MN:MO:NE:ND:OH:SD:WI", BillingState), "North", "Other"))))))
- This formula embeds a link with the HYPERLINK() function. Generally, HYPERLINK(url, display_text, target) displays a link to url with display_text. The optional target argument determines how the web page displays when the link is clicked. If you leave target blank, the link opens in a new browser window by default.
- This formula takes the field Email and uses it to create a URL. The formula uses FIND() and SUBSTITUTE() to find the @ sign in the email address and replace everything to the left of it with www.
SUBSTITUTE(Email, LEFT(Email, FIND("@", Email)), "www.")
Common Errors with Text Formulas
Converting values to text. Make sure that you check a field’s type before you use it in a formula. For example, a previous Salesforce administrator could have created a custom field that holds a date as a Text value. If that’s the case, you can use TEXT() or DATEVALUE() to work with Text and Date values in tandem.
Helper Formula Fields
Using helper formula fields is a great way to make your formulas short and easy to understand. A helper field does only part of a larger calculation and is referenced by the end formula field. You can use helper formula fields with any type of formula, but let’s look at an example right now.
Let’s say you want to write a text formula called Case_Category__c that displays a category for a case (Green, Yellow, or Red) depending on how long that case has been open. Normally, you might write something like this.
IF(((NOT(IsClosed)) && (TODAY() - DATEVALUE(CreatedDate) > 20)), "RED", IF((NOT(IsClosed)) && (TODAY() - DATEVALUE(CreatedDate) > 10), "YELLOW", "GREEN"))
TODAY() - DATEVALUE(CreatedDate)
IF(((NOT(IsClosed)) && (Case_Age_In_Days__c > 20)), "RED", IF((NOT(IsClosed)) && (Case_Age_In_Days__c > 10), "YELLOW", "GREEN"))