TEXT() Function in Salesforce

In this Salesforce tutorial, we will learn how to use the TEXT() function in Salesforce to convert a value to text using standard display format. We will also learn its syntax and a few real-world business-related useful examples.

Recently, I got hired by a multinational company located in California and they make use of Salesforce as a CRM application. One day I got the task to display the message that shows the number of days left in the expiry of the subscription.

So, as a solution, I found that Salesforce provides a function named TEXT which helped me to display this text message. If you want to learn how how to use the TEXT() function in Salesforce Lightning and Salesforce Classic components let’s deep dive into this tutorial and learn with examples.

What is TEXT() Function in Salesforce

The TEXT() function in Salesforce is one of the text functions available in Salesforce that is used to carry out text operations. In basic terms, the TEXT() function is used when we need to display or modify the data in the textual format.

In other words, we can say that this TEXT() function is used to convert the percent, number, date, date/time, currency, and picklist field type data into text field type.

The syntax of the TEXT() function is as follows:

TEXT(value)

Here, the value parameters specify the value or field that we want to transform into text format. It could be a constant value, a formula, or a field reference.

For a better understanding of how the TEXT function works, let’s look at an example.

As I mentioned above, I need to display the message that shows the number of days left in the expiry of the subscription. Therefore, I will utilize Salesforce TEXT() function for this. The formula is as given below:

IF(
  Subscription_Expiry_Date__c >= TODAY(),
  "Product Expired",
  "Number of Days Remaining: " & TEXT(Subscription_Expiry_Date__c - TODAY())
)

With this, we learned the syntax of the TEXT() function as well as an example. We’ll now look at how to use the TEXT() function in Salesforce Lightning.

Read: BEGINS() Function in Salesforce

How to use TEXT() Function in Salesforce Lightning

The following are the steps to use the TEXT() function in Salesforce Lightning.

Step 1: Open Salesforce Lightning mode. Next, click the “Gear Icon” in the top right corner of the page. Click “Setup” from the dropdown menu to continue.

Step 2: The setup page is opened after selecting the Setup option. Here, the “Object Manager” is found in the Navigation Bar next to the Home tab. Click on it.

Step 3: The object manager page is opened after you click it. There are numerous objects seen here. Use the quick find search field to look for the item on which we want to use the TEXT() function. In this instance, I look for the “Product” object because I need to convert the product price into the text and also want to concatenate the USD text to it.

Step 4: Now click the “Product” object to move to the product object page.

Step 5: Under this product object page, on the left side of the page, the “Field & Relationship” option is found in the details section. Select “Field & Relationship” from the menu.

Step 6: The field and relationships page is opened after selecting the field and relationship option. Click the “New” button at the top of the page.

Step 7: The field type page is opened after clicking the new button. The page contains a wide variety of field types. In this case, I choose the “Formula” field type field, as I want to use the TEXT function in the advanced formula.

Step 8: Click the “Next” button to move on to the next step.

Step 9: The choose output type screen appears after clicking the next button. Enter the information now by filling out the “Field Label” and the “Field Name”, which is automatically filled in when you put the cursor on the field name. In this instance, I typed “Total Product Price” into the field label.

Step 10: Choose the “Output Data Type” for the field in which we want to store the result. In this case, I check the radio button in front of the “Text” data type to receive the formula return type as a text.

Step 11: Move to the next, click on the “Next” button.

Step 12: Go to the advanced formula editor and enter the TEXT() function formula there. In this instance, I want to use the TEXT function to convert the currency into text format and also to concatenate the text into it. The formula is as follows:

TEXT(Price__c + Shipping_Price__c) & ' USD'

Here:

• Firstly we calculate the sum of two fields product price and shipping price.
• Next, we pass the summed value to the TEXT function to convert the result of the addition into a text format.
• After that, we use the & operator to combine the text result with the text “USD”.

Step 13: To validate the formula and make sure there are no mistakes, click the “Check Syntax” option.

Step 14: Optionally, we may define the “Description” and “Help text” as well as “Handle the empty field” if we like.

Step 15: Click on the “Next” button to go to the security setup for the formula field.

Step 16: In this step, establish “Field-Level Security”. Choose the profiles to which we wish to grant field-level security edit access to this field. If field-level security is not added, the field will be hidden from all profiles.

As I want it to be visible to all profiles, I have checked the “Visible” checkbox in this instance. After that, click the “Next” button located at the top.

Step 17: Add the custom field to the “Page Layout” after setting up the field-level security. Pick the page layout that has this field in it. If we do not select a layout, the field will not appear on any pages.

Step 18: To save the formula field, click the “Save” button.

We can use the formula field once we have created it. Let’s look at an example:

• Open the Products Tab and create a new product with the field Price and Shipping Price.
• The Total Product Price field, which displays the output of the TEXT() function, then appears when you click on the Details Section.

As a result, we now understand how to use the TEXT function in Salesforce Lightning. Now, we will look at how to use the TEXT function in Salesforce Classic.

Read ASCII() Function in Salesforce

How to use TEXT() Function in Salesforce Classic

The following are the steps to use the TEXT() function in Salesforce Classic.

Step 1: Log in to your Salesforce account first, after you are logged in, the Salesforce Lightning Edition should already be open. Click “Profile Picture” once you are in Salesforce Lightning. The drop-down menu for the profile photo is available. There is a “Switch To Salesforce Classic” option below the selection of profile picture. Click on it.

Step 2: Next, click on the “Setup” option from the menu.

Step 3: The setup page is opened after selecting the setup option. Go down to the “Build” section now. Select the object for whose field we want to use the TEXT() function under the “Customize” dropdown. In this instance, I choose the “Campaign” object since I need to print the message about whether the upcoming campaign is possible on that date or not.

Step 4: Select “Campaign” from the drop-down menu. The drop-down list is available, and under it, there is a field option. Select “Fields” from the menu.

Step 5: The product custom field page is opened after selecting the fields option. There is a section titled “Campaign custom field and relationship” at the bottom of the page. Simply click the “New” button to add a new field.

Step 6: The field type page is opened after clicking the new button. The page contains a wide variety of data types. In this case, I am choosing the “Formula” data type field.

Step 7: Click the “Next” button to move on to the following step.

Step 8: The “Choose Output Type” window appears after selecting the next button. Fill out the “Field Label” and “Field Name” (which are pre-filled when you click on the field name) to enter the information. In this case, I entered “Upcoming Campaign Possibility” into the field label.

Step 9: Choose the “Output Data Type” for the field in which we want to store the result. In this case, I check the radio button in front of the “TEXT” data type to receive the formula return type as a text.

Step 10: Click the “Next” button to move on to another step.

Step 11: Next, enter the TEXT() function formula in the advanced formula editor. In this case, I wish to utilize the TEXT function, to print the upcoming campaign possibility based on the upcoming date of the campaign. The formula reads as follows:

IF(
    OR(
        MOD(WEEKDAY(Upcoming_Campaign_Date__c) - 1, 7) = 0,
        MOD(WEEKDAY(Upcoming_Campaign_Date__c) - 1, 7) = 6
    ),
    TEXT(Upcoming_Campaign_Date__c) & "It's a weekend, not possible. Try the next date.",
    "Booking is possible"
)

Here:

• We pass the Upcoming_Campaign_Date field to the WEEKDAY() function which returns the value that represents the day of the week.
• Next, the MOD() function determines the remainder when we subtract 1 from the Weekday resultant value. Basically, it represents Monday as the first day of the week instead of Sunday.
• Then, the OR() function is used to check the conditions which are as follows. If any one of the conditions is true, it means that the Upcoming_Campaign_Date field is the weekend.
  • First, it checks whether the resultant value is 0 i.e. the date is Sunday.
  • Second, it checks whether the resultant value is 6 i.e. the date is Saturday.
• After that, we use the IF() function that checks if the date is a weekend.
  • If it is the TEXT() function converts the Upcoming_Campaign_Date field to text and prints the message It’s a weekend, not possible. Try the next date.
  • Otherwise, it prints the message Booking is possible.

Step 12: Click “Check Syntax” to validate the formula and ensure there are no errors.

Step 13: Define the “Description” and “Help text” as well as “Handle the empty field” if you would like.

Step 14: To access the security page for the formula field, click the “Next” button.

Step 15: Establish “Field-Level Security”. Select the profiles to whom you want to grant field-level security editing access. The field will be hidden from all profiles if field-level security is not set.

I have selected the “Visible” checkbox in this case because I want it to be visible to all profiles. In order to continue, click the “Next” button at the top.

Step 16: After configuring the field-level security, add the custom field to the “Page Layout”. Choose the page layout that includes this field. The field would not show up on any pages if we choose not to select a layout.

Step 17: To save the formula field, click the “Save” button.

Once we have established the formula field, we may use it. Let’s examine an illustration:

• Create a new campaign with the fields Upcoming Campaign Date by opening the Campaign Tab.
• Click on the Details section after that, and the Upcoming Campaign Possibility field will display the TEXT() function output.

We now understand how to use the TEXT() function in Salesforce Classic.

Conclusion

We have learned the Salesforce TEXT() function. Moreover, we have learned the TEXT() function in Salesforce syntax and several real-world business scenarios. In addition, we also covered the steps for using the TEXT() Function in Salesforce Lightning and Salesforce Classic.

You may like to read:

• TRUNC() Function in Salesforce
• ATAN2() Function in Salesforce
• Salesforce Case Sensitivity For Text Field Type