Use Text for Valuable Insights
Learning Objectives
After completing this unit, you’ll be able to:
- Convert text values to or from other data types.
- Describe use cases for formulas using the Text type.
- Create a formula using the Text return type.
Text in Formulas
Text return types are one of the most dynamic options for displaying data. There are a lot of options for manipulating text and other data types to show complicated data insights in a simple way. Formulas that use the text return type can concatenate text values, convert numbers and dates to text, display images, create hyperlinks, or display text conditionally. Let’s look at the simplest option and then dig into some more dynamic options.
Text Functions and Operators
One of the simplest things you can do is concatenate two Text strings with the (&) concatenate operator. For example, you can display a contact’s full name by combining the first and last name.
FirstName & " " & LastName
When putting text values together, don’t forget to insert spaces between words: “Jane Doe” is a lot more readable than “JaneDoe.” This explains the added quotation marks to insert the space.
Convert to and from Text Values
The TEXT function converts a percent, number, date, date/time, picklist, or currency field into a text value. TEXT returns output without any formatting, commas, or currency signs. For example, if percent_value is set to 30%, TEXT(percent_value)returns 0.3. Look at these two examples using the date and datetime data types.
Given Value |
Formula |
Return Value |
|---|---|---|
|
|
2015-03-17 |
|
|
2015-03-17 17:00:00Z * |
*The Z indicates that the time returned is in Coordinated Universal Time or UTC—text always returns date/time values in UTC, not the time zone of the current user or the time zone of your organization.
Positional 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.
Use the Text Data Type in Formulas
Full Name with Salutation
Time for you to see this in action. The marketing team wants you to help them out like you did for the sales team. They want a single field they can use for a contact’s full name that includes the salutation if the contact has one specified. You can do that, right? Of course you can!
- In Setup, open the Object Manager and select Contact.
- Select Fields & Relationships and select New.
- Select Formula and click Next.
- For Field Label, enter
Full Name. Field Name populates automatically.
- Select Text then click Next.
- Select Advanced Formula.
First you need to check for a value in the salutation picklist. If the salutation is blank then you want to return a blank string, or the salutation with a space after it.
- Insert an IF function.
- Update the IF parameter
logical_testwith the ISPICKVAL function.
- For the ISPICKVAL parameter
picklist_fieldinsert the Salutation field.
- Replace the
text_literalparameter with blank quotes"".
- Replace the IF parameter
value_if_truewith blank quotes"".
Your formula should be:IF( ISPICKVAL(Salutation, ""), "", value_if_false)
- For the
value_if_falseparameter, insert the TEXT function and insert the Salutation field for itsvalueparameter.
Your formula should be:IF( ISPICKVAL(Salutation, ""), "", TEXT( Salutation ))
The IF function will return a blank space or the Salutation if there is one. There needs to be space between the Salutation and the first name so you can add that right after the TEXT function in the IF function.
- Right after the last TEXT function parenthesis and inside the last IF parenthesis add the Concatenate operator along with
" ".
Your formula should be:IF( ISPICKVAL(Salutation, ""), "", TEXT( Salutation ) & " ")
- Now after the IF function, insert another Concatenate operator and insert the First Name field.
- Then insert another Concatenate operator and enter
" "followed by another Concatenate operator.
- Finally, insert the Last Name field.
Your formula should be:IF( ISPICKVAL(Salutation, ""), "", TEXT( Salutation ) & " ") & FirstName & " " & LastName
- Select Check Syntax.
- For Description enter
Full name with salutation if there is one.
- Set Blank Field Handling to Treat blank fields as blanks.
- Select Next, Next and Save.
You’ve done it again! You empowered the marketing team by simplifying their processes.
Display an Image
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 better than text alone. Let’s impress the sales team again and write a formula that uses the Score field that you created in the previous unit to display a lead’s score graphically as zero, one, three, or five out of five stars.
The IMAGE function takes up to four inputs. IMAGE(image_url, alternate_text, height, width) displays the image specified by image_url with the dimensions specified by the optional height and width parameters. If the image can’t be displayed, the function displays alternate_text.
If the lead Score is:
- 30, the formula shows five stars

- 20, the formula shows three stars

- 10, the formula shows one star

- If the lead Score is 0, the formula shows no stars

You use the built in images provided by Salesforce for this one, or you can use your own images by first uploading them from the Documents tab. Go back to the Lead object and create the new formula field.
- In Setup, open the Object Manager and select Lead.
- Select Fields & Relationships and click New.
- Select Formula and click Next.
- For Field Label, enter
Star Score. Field Name populates automatically.
- Select Text then click Next.
- Select Advanced Formula.
For this formula you’re going to replace the image_url parameter of the IMAGE function with a CASE function to choose the right image to display. We used the CASE function in the Quick Data Insights with Formulas module if you want to get an understanding for its use.
- Start by inserting the CASE function.
- Replace the CASE
expressionparameter with the Score field.
Your formula should be:
CASE( Score__c , value1, result1, value2, result2, ..., else_result)
Now you add the score amounts and the corresponding images for each value and result in the CASE.
- Replace the
value1parameter with30and theresult1parameter with an IMAGE function.
Your formula should be:
CASE( Score__c , 30, IMAGE(image_url, alternate_text [, height, width]), value2, result2, ..., else_result)
- For the IMAGE
image_urlparameter insert"/img/samples/stars_500.gif".
- Then replace the
alternate_textparameter with"5 Stars"and remove the[, height, width]parameters, you don’t need them for this.
Your formula should be:
CASE( Score__c ,
30, IMAGE("/img/samples/stars_500.gif", "5 Stars"),
value2, result2,
...,
else_result)- Replace the
value2with20along withresult2with another IMAGE function.
- Update the IMAGE parameters with
"/img/samples/stars_300.gif","3 Stars", and remove the[, height, width]parameters.
- For
...enter10, IMAGE("/img/samples/stars_100.gif", "1 Stars").
- For
else_resultenterIMAGE("/img/samples/stars_000.gif", "0 Stars")for the final image if the score is 0.
Your formula should be:
CASE(Score__c,
30, IMAGE("/img/samples/stars_500.gif", "5 Stars"),
20, IMAGE("/img/samples/stars_300.gif", "3 Stars"),
10, IMAGE("/img/samples/stars_100.gif", "1 Stars"),
IMAGE("/img/samples/stars_000.gif", "0 Stars")
)- Click Check Syntax.
- For Description enter
Graphical display of Score.
- Leave Blank Field Handling to Treat blank fields as zeroes.
- Click Next, Next and Save.
You crushed it. Nice work! You’ll use text data types a lot since there is so much you can do with them. Here are some other ideas.
Text Examples
Here are some other examples of ways to use the text data type.
- This formula uses the Billing State/Province field of a U.S.-based account to classify it as North, South, East, West, or Central. It uses a series of nested IF functions and the CONTAINS function.
IF(ISBLANK(BillingState), “None”,
IF(CONTAINS(“AK:AZ:CA:HI: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 tourlwithdisplay_text. The optionaltargetparameter determines how the web page displays when the link is clicked. Leavingtargetblank opens in a new browser window by default.HYPERLINK(“https://www.salesforce.com”, “Salesforce”)
- 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.")
You’re doing great! Let’s keep building your skills in the next unit with dates and times.