Skip to main content
Die Trailblazer Community auf Slack befindet sich jetzt in der offenen Betaphase – treten Sie bei und tauschen Sie sich in Echtzeit aus. Kommen Sie zu uns auf Slack

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

date_value = March 17, 2015

TEXT(date_value)

2015-03-17

datetime_value = March 17, 2015 at 5 PM

TEXT(datetime_value)

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!

  1. In Setup, open the Object Manager and select Contact.
  2. Select Fields & Relationships and select New.
  3. Select Formula and click Next.
  4. For Field Label, enter Full Name. Field Name populates automatically.
  5. Select Text then click Next.
  6. 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.

  1. Insert an IF function.
  2. Update the IF parameter logical_test with the ISPICKVAL function.
  3. For the ISPICKVAL parameter picklist_field insert the Salutation field.
  4. Replace the text_literal parameter with blank quotes "".
  5. Replace the IF parameter value_if_true with blank quotes "".
    Your formula should be:
    IF( ISPICKVAL(Salutation, ""), "", value_if_false)
  6. For the value_if_false parameter, insert the TEXT function and insert the Salutation field for its value parameter.
    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.

  1. 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 ) & " ")
  2. Now after the IF function, insert another Concatenate operator and insert the First Name field.
  3. Then insert another Concatenate operator and enter " " followed by another Concatenate operator.
  4. Finally, insert the Last Name field.
    Your formula should be:
    IF( ISPICKVAL(Salutation, ""), "", TEXT( Salutation ) & " ") & FirstName & " " & LastName
  5. Select Check Syntax.
  6. For Description enter Full name with salutation if there is one.
  7. Set Blank Field Handling to Treat blank fields as blanks.
  8. 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.

  1. In Setup, open the Object Manager and select Lead.
  2. Select Fields & Relationships and click New.
  3. Select Formula and click Next.
  4. For Field Label, enter Star Score. Field Name populates automatically.
  5. Select Text then click Next.
  6. 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.

  1. Start by inserting the CASE function.
  2. Replace the CASE expression parameter 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.

  1. Replace the value1 parameter with 30 and the result1 parameter with an IMAGE function.
    Your formula should be:
CASE( Score__c ,
  30, IMAGE(image_url, alternate_text [, height, width]),
  value2, result2,
  ...,
  else_result)
  1. For the IMAGE image_url parameter insert "/img/samples/stars_500.gif".
  2. Then replace the alternate_text parameter 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)
  1. Replace the value2 with 20 along with result2 with another IMAGE function.
  2. Update the IMAGE parameters with "/img/samples/stars_300.gif", "3 Stars", and remove the [, height, width] parameters.
  3. For ... enter 10, IMAGE("/img/samples/stars_100.gif", "1 Stars").
  4. For else_result enter IMAGE("/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")
)
  1. Click Check Syntax.
  2. For Description enter Graphical display of Score.
  3. Leave Blank Field Handling to Treat blank fields as zeroes.
  4. 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 to url with display_text. The optional target parameter determines how the web page displays when the link is clicked. Leaving target blank 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.

Resources

Teilen Sie Ihr Trailhead-Feedback über die Salesforce-Hilfe.

Wir würden uns sehr freuen, von Ihren Erfahrungen mit Trailhead zu hören: Sie können jetzt jederzeit über die Salesforce-Hilfe auf das neue Feedback-Formular zugreifen.

Weitere Infos Weiter zu "Feedback teilen"