Text Functions for Formula Fields in Salesforce
Introduction
Hello everyone, Welcome back. We hope you are doing well. In our last blog, we discussed ”Date/Time & Advanced Functions for Formula Fields in Salesforce”. Today we will be talking about various Text Functions for Formula Fields in Salesforce.
Just to make you recall formula functions can only be used in Advanced Formulas and are categorized into five types, i.e.
Date & Time
Text
Math
Logical
Advanced
So without further ado, let’s start.
For your information, to explain the purpose, we have created a different custom object for each category where we will create another formula field with each formula function.
For Text functions, we have created an “Our Text” object for the Text category with one Text type field, “Text Input by User,” to provide its input.
Text Functions
Begins & BR Function
The BEGINS function determines if text begins with specific characters and returns TRUE if it does otherwise returns FALSE if it doesn't. The syntax of this function is-
So, from the syntax, it’s clear that you have to pass two texts as parameters to the function. This function returns true if the text1 starts with text2 otherwise false.
For example, we have created a formula field in our object named ‘Begins with Vowel’ with Checkbox return type which will show true if the ‘Text Input By User’ field starts with a vowel otherwise false.
The following formula we have used for this field.
BEGINS( Text_Input_by_User__c , 'A') || BEGINS( Text_Input_by_User__c , 'E') || BEGINS( Text_Input_by_User__c , 'I') || BEGINS( Text_Input_by_User__c , 'O') || BEGINS( Text_Input_by_User__c , 'U')
Well there is a catch: it will only work if the first character is in uppercase otherwise it won’t. We can modify the formula a bit and it would work for any case and that is by using the UPPER function for Text_Input_by_User__c field as below.
BEGINS( UPPER(Text_Input_by_User__c) , 'A') || BEGINS( UPPER(Text_Input_by_User__c) , 'E') || BEGINS( UPPER(Text_Input_by_User__c) , 'I') || BEGINS( UPPER(Text_Input_by_User__c) , 'O') || BEGINS( UPPER(Text_Input_by_User__c) , 'U')
The BR function Inserts a line break in a string of text. The syntax of this function is -
For example, we have created a formula field in our object named ‘Message’ with Text return type which will show the modified text with help of ‘Text Input by User’ and ‘Created By Id’ field.
The following formula we have used for this field.
Text_Input_by_User__c + ' is Created By ' + BR() + CreatedById
Please find the following screenshot to understand the output of these formula fields.
Points to be noted with the BEGINS function.
This function is case-sensitive so be sure your compare_text value has the correct capitalization.
When using this function in a validation rule or workflow rule, fields that are blank are considered valid.
Points to be noted with the BR function.
Don't remove the parentheses after the function name.
Keep the parentheses empty. They don't contain values.
Remember to surround the BR() with concatenation operators: & or +.
Avoid using this function in mail merge templates.
This function isn't available in custom buttons and links, s-controls, or reports.
Contains, Find & Includes Functions
The CONTAINS function compares two arguments of text and returns TRUE if the first argument contains the second argument. If not, return FALSE. The syntax of this function is -
So, from the syntax it’s clear that you have to pass two texts as parameters to the function. This function returns True if the text1 contains text2 in it, otherwise False.
For example, we have created a formula field in our object named ‘Contains Articles’ with a checkbox return type which will show ‘True’ if the ‘Text Input By User’ field contains articles (a, an or the) otherwise false.
The following formula is used in this field.
CONTAINS( UPPER(Text_Input_by_User__c) , 'A') || CONTAINS( UPPER(Text_Input_by_User__c) , 'AN') || CONTAINS( UPPER(Text_Input_by_User__c) , 'THE')
The FIND function returns the position of a string within a string of text represented as a number. The syntax of this function is -
So, from the syntax it’s clear that you have to pass two texts as parameters to the function. This function returns the position of the first occurrence of text1 in the text2.
For example we have created one text field named ‘Text to Find’ and one formula field of number return type named ‘Position’ which will show the position of the first occurrence of the text of ‘Text to Find’ field in the ‘Text Input by User’ field.
The following formula is used in this field.
FIND(Text_to_Find__c , Text_Input_by_User__c)
The INCLUDES function determines if any value selected in a multi-select picklist field equals a text literal you specify. The syntax of this function is -
[ms_pf means multi picklist field]
So, from the syntax it’s clear that you have to pass a multi select picklist field and a text to search in that field.
For example, we have created a multi-select picklist field ‘Subjects’ in our object with some subject names and a formula field ‘Apex Selected’ of checkbox return type and it will show true if the multi-select picklist has Apex Selected.
The following formula is used in this field.
INCLUDES( Subjects__c , 'Apex')
Please find the following screenshot to understand the output of these formula fields.
Points to be noted with the CONTAINS function.
This function is case-sensitive so be sure your compare_text value has the correct capitalization.
When using this function in a validation rule or workflow rule, fields that are blank are considered valid. For example, if you have a validation rule that tests to see if the serial number of an asset contains “A,” all assets that have a blank serial number are considered valid.
The CONTAINS function doesn't support multi-select picklists. Use INCLUDES to see if a multi-select picklist has a specific value.
Points to be noted with the FIND function.
Be sure to remove the brackets, [ and ], from your formula before validating it.
If the field referenced in your text parameter is blank, the formula field displays 0.
Your search_text parameter is case-sensitive and can't contain any wildcard characters.
If your search doesn't return any results, a 0 displays in the field.
The start_num parameter is optional. If you don't enter a start_num value, the formula uses the value one, or the first character in the string.
If your start_num isn't greater than zero, a 0 displays in the field.
If your start_num is greater than the length of the text, a 0 displays in the field.
When entering your start_num parameter, remember that some fields like the Website field are unique because a http:// is automatically appended to the beginning of the text you enter.
The first character in a string is designated as one rather than zero.
Points to be noted with the INCLUDES function.
The text_literal expression must be of type text and enclosed in quotes. It cannot be a merge field or the result of a function.
Salesforce returns an error if any of the following occurs:
You do not provide a text_literal expression.
You provide an empty text_literal expression, such as "" or " ".
Use ISBLANK to determine if a multi-select picklist field is empty.
Use the PRIORVALUE function inside the INCLUDES function to check if the previous value of a multi-select picklist field included a specific value.
CaseSafeId & GetSessionId Functions
The CASESAFEID function converts a 15-character ID to a case-insensitive 18-character ID. The syntax of this function is-
So, from the syntax it’s clear that you have to pass an id and it will return an 18 character id.
For example, we have created a ‘Fifteen Character Id’ field having a fifteen character id and a formula field ‘Eighteen Character Id’ that will show the 18-character id from the ‘Fifteen character id’ field.
The following formula is used in this field.
CASESAFEID( Fifteen_Character_ID__c )
The GETSESSIONID function returns the user’s session ID. The syntax of this function is-
So from the syntax it’s clear that this function returns the current user’s session id.
For example, we have created a formula field named ‘Current User Session Id’ to show the current session id of the user.
Please find the following screenshot to understand the output of these formula fields.
Points to be noted with the CASESAFEID function.
Convert to 18-character IDs for better compatibility with Excel.
The CASESAFEID function is available everywhere that you can define a formula except reports and s-controls.
Points to be noted with the GETSESSIONID function.
Session identifiers from different contexts, and the sessions themselves, are different. When you transition between contexts, the old session is replaced by the new one, and the old session is no longer valid. The session ID also changes at this time.
Normally Salesforce transparently handles session hand-off between contexts, but if you’re passing the session ID around yourself, you might need to re-access $Api.Session_ID or GETSESSIONID() from the new context to ensure a valid session ID.
Not all sessions are created equal. In particular, sessions obtained in a Lightning Experience context have reduced privileges, and don't have API access. You can't use these session IDs to make API calls. {!$Api.Session_ID} isn’t generated for guest users.
Hyperlink & Image Functions
The HYPERLINK function creates a link to a URL specified that is linkable from the text specified. The syntax of this function is -
So, from the syntax it’s clear that we need to pass a URL along with a friendly name and this function will return a link to that URL. This function also accepts an optional parameter as target. The value of the target parameter can be _blank, _self, _parent and _top.
For example, we have created a formula field named ‘Link to Record’ which will provide a link to the record.
The following formula is used in this field.
HYPERLINK('/'+ Id , Text_Input_by_User__c , '_blank')
The IMAGE function inserts an image with alternate text and height and width specifications. The syntax of this function is -
So, from the syntax it’s clear that you need to pass image_url, alternate_text, height and width for the image to be displayed. Note that height and width are optional parameters to this field.
For example, we have created an ‘Image URL’ field containing the url for the image and a formula field named ‘Image’ to show the image using this formula.
The following formula is used in this field.
IMAGE( Image_URL__c , Id+'_Image', 100, 100)
Please find the following screenshot to understand the output of these formula fields.
Points to be noted with the HYPERLINK function.
Hyperlink formula fields are of type text.
Include the protocol and URL in quotes as in HYPERLINK("http://www.cnet.com", "cnet").
Avoid using text functions such as LEN, LEFT, or RIGHT on HYPERLINK function results.
The URL can’t contain JavaScript. This increases security for your org. Using JavaScript is permitted in packages, sandbox copies, and change sets.
Use a relative link to link to Salesforce pages. If your full link is https://yourInstance.salesforce.com/00U/e, then its relative link is /00U/e. Relative links allow the hyperlink to work correctly on all Salesforce pages. Use the relative URL in a hyperlink formula to add it to a search layout. Make sure to prepend your relative URL with a forward slash “/”.
Use the $Api variable to reference API URLs.
Be sure to remove the brackets, [ and ], from your formula before validating it.
The target parameter is optional. If you don't specify a target, the link opens in a new browser window. Some common target parameters are:
_blank: Displays link in a new unnamed window.
_self: Displays link in the same frame or window as the element that refers to it.
_parent: Displays link in the immediate frameset parent of the current frame. This value is the same as _self if the current frame has no parent.
_top: Displays link in the full original window, canceling any other frames. This value is the same as _self if the current frame has no parent.
The HYPERLINK function is available everywhere that you can define a formula except default values, field updates, s-controls, validation rules, approval processes, custom buttons and links, and workflow rules.
Points to be noted with the IMAGE function.
The height and width parameters are optional.
Use a text string to replace the image_url and alternate_text parameters. Surround each text string in quotes.
Use numbers to replace the height and width parameters.
Add images to your Documents tab if you want to display them elsewhere. For example, store the image of a product in a document folder, copy the URL to the document, and paste that URL in the image_url parameter of a formula field on the Products tab.
If you use Internet Explorer, you sometimes must change your security settings so that Explorer doesn’t display a warning prompt when images use HTTP protocol. See the online help for Internet Explorer for instructions on changing your security settings.
The IMAGE function cannot include the GETSESSIONID function as one of its arguments.
The IMAGE function is available only in formula fields and email templates.
You can’t display an image related to a contact in a custom formula field if it’s referenced through a person account.
Left, Mid & Right Functions
The LEFT, MID and RIGHT functions are used to return the specified number of characters from the beginning, middle and end of the text string. The syntax of these functions are -
From the above syntax it's clear that the LEFT function replaces text with the field or expression you want returned; replace num_chars with the number of characters from the left you want returned. The MID function replaces text with the field or expression to use when returning characters; replace start_num with the number of characters from the left to use as a starting position; replace num_chars with the total number of characters to return. The RIGHT function replaces text with the field or expression you want returned; replace num_chars with the number of characters from the right you want returned.
For example, we have created a field named ‘Number of Characters’ and three formula fields for LEFT, MID and RIGHT functions named ‘From Left’, ‘In Mid’ and ‘From Right’ respectively.
The following formulas are used in these fields.
LEFT( Text_Input_by_User__c , Number_of_Characters__c )
MID( Text_Input_by_User__c , LEN(Text_Input_by_User__c)/2 , Number_of_Characters__c )
RIGHT( Text_Input_by_User__c , Number_of_Characters__c )
[NOTE- LEN function is used to return the number of characters of a text passed as parameter]
Please find the following screenshot to understand the output of these formula fields.
Points to be noted with the LEFT and RIGHT functions.
Reference auto-number fields as text fields in formulas.
If the num_chars value is less than zero, Salesforce replaces the value with zero.
LPad, RPad & Trim Functions
The LPAD and RPAD functions are used to insert characters you specify to the left-side and right-side of a text string respectively. The syntax of these functions are -
From the above syntax it’s clear that these functions insert the pad_string characters on the left side or right side of the text to make the total length equal to padded length. The parameters here means that -
text is the field or expression you want to insert characters to the left of.
padded_length is the number of total characters in the text that will be returned.
pad_string is the character or characters that should be inserted. pad_string is optional and defaults to a blank space.
For example, we have created a field named ‘Padded Length’ that will specify the number of characters to be padded and two formula fields ‘Left Padded’ and ‘Right Padded’.
The formulas used in these fields are -
LPAD(Text_Input_by_User__c , Padded_Length__c , '@')
RPAD(Text_Input_by_User__c , Padded_Length__c , '@')
The Trim Function removes the spaces and tabs from the beginning and end of a text string. The syntax of this function is-
From the above syntax it’s clear that we need to pass a text to this function and it returns all the blank spaces from both sides.
For example, we have created a ‘Trimmed Text’ field to show the trimmed text from the field Text Input by User.
The formula used in this field is-
TRIM(Text_Input_by_User__c)
Please find the following screenshot to understand the output of these formula fields.
Points to be noted with the LPAD and RPAD functions.
Leading blank spaces and zeros are omitted for LPAD.
Ending blank spaces are omitted for RPAD..
Lower, Upper, Text & Value Functions
The LOWER and UPPER functions are used to convert the text passed to this function to lowercase and uppercase respectively. The syntax of these functions are -
From the above syntax it’s clear that these functions accept text in any case and return them in lowercase and uppercase respectively.
For example, we have created ‘Lowercase Text’ and ‘Uppercase Text’ fields to show the uppercase and lowercase text of the text Input by the user.
Formula used in these fields are-
LOWER(Text_Input_by_User__c)
UPPER(Text_Input_by_User__c)
The Text function converts a percent, number, date, date/time, or currency type field into text anywhere formulas are used. Also, converts picklist values to text in approval rules, approval step rules, workflow rules, escalation rules, assignment rules, auto-response rules, validation rules, formula fields, field updates, and custom buttons and links.
The value function converts a text string to a number. You can say it does just the opposite of the TEXT function.
Please find the following screenshot to understand the output of the LOWER and UPPER formula fields.
Points to be noted with the TEXT function.
Use ISBLANK instead of ISNULL in new formulas. ISBLANK has the same functionality as ISNULL, but also supports text fields. Salesforce will continue to support ISNULL, so you do not need to change any existing formulas.
A field is not empty if it contains a character, blank space, or zero. For example, a field that contains a space inserted with the spacebar is not empty.
Use the BLANKVALUE function to return a specified string if the field doesn't have a value; use the ISBLANK function if you only want to check if the field has a value.
If you use this function with a numeric field, the function only returns TRUE if the field has no value and is not configured to treat blank fields as zeroes.
If you use this function with a picklist, use ISBLANK(TEXT(<picklist>)) to convert the picklist items into a text value.
Points to be noted with the VALUE function.
Make sure the text in a VALUE function doesn’t include special characters other than a decimal point (period) or minus sign (dash). If the text in a VALUE function is a non-numerical/invalid format, the formula isn’t calculated and resolves to a blank value. For example, the formula 1 + VALUE(Text_field__c) produces these results:
If Text field is 123, the result is 124.
If Text field is blank, the result is blank.
If Text field is $123, the result is blank.
If Text field is EUR123, the result is blank.
IsPickVal & Substitute Functions
The ISPICKVAL function determines if the value of a picklist field is equal to a text literal you specify. The syntax of this function is
From the above syntax it’s clear that we need to pass a picklist field and a text value and this function will return true if the selected picklist field value matches the text.
For example, we have created a ‘Direction’ picklist field with North, South, East and West options and four formula fields ‘Is North’, ‘Is South’, ‘Is East’ and ‘Is West’ fields.
The formula used in these fields are -
ISPICKVAL(Direction__c, 'North'), ISPICKVAL(Direction__c, 'South'), ISPICKVAL(Direction__c, 'East'), ISPICKVAL(Direction__c, 'West')
The Substitute function substitutes new text for old text in a text string. The syntax of this function is-
From the above syntax it’s clear that it replaces text with the field or value for which you want to substitute values, old_text with the text you want replaced, and new_text with the text you want to replace the old_text.
For example, we have created two formula fields, ‘Direction Text’ which will display text like ‘North Direction’ and a ‘Substituted Text’ field which will show results after substituting ‘Direction’ with ‘Side’.
Formula used in these fields are
TEXT(Direction__c)+ ' Direction'
SUBSTITUTE( Direction_Text__c , 'Direction', 'Side')
Please find the following screenshot to understand the output of these formula fields.
Points to be noted with the ISPICKVAL function.
Replace picklist_field with a custom or standard field of type picklist.
Your text_literal expression must be of type text and enclosed in quotes. It cannot be a merge field or the result of a function.
Use CASE functions to determine if a picklist value is equal to a particular value.
Points to be noted with the SUBSTITUTE function.
Each term provided in quotes is case-sensitive.
If the old_text appears more than once, each occurrence is replaced with the new_text value provided, even when that results in duplicates.
Whether you're a newbie or pro, we hope this blog was helpful to help get your creative juices flowing. If there are any questions, feel free to leave them in the comments below. till then check out the previous Salesforce blogs 👋
