SUBSTITUTE Function in Salesforce | Replace Values in Salesforce Formula Field

In this Salesforce Tutorial, we will learn how to use the SUBSTITUTE function to replace values in the Salesforce formula Field. In addition, we will learn the syntax and business-related use cases where we can use the Salesforce SUBSTITUTE function.

You all know that Salesforce is one of the best CRM tools, and we store multiple business-related records in Salesforce using manual data entry, data import, external systems, etc.

Maybe you have observed that while storing the data some fields contain inconsistent or non-standardized data values such as the in-country field Some records have a value as USA some records have a value as United States of America and some records only contain the value as US.

Due to these inaccuracies, it becomes difficult to analyze the data. So, to overcome these issues and clean the data’s inconsistent values, the Salesforce SUBSTITUTE function comes into the role.

If you want to learn how to use the Salesforce SUBSTITUTE function with examples in both Salesforce Lightning and Salesforce Classic you have come to the right place. Let’s go to the complete article and try to gain all the knowledge related to the Salesforce SUBSTITUTE function.

Define Salesforce SUBSTITUTE Function

In Salesforce, we have various TEXT functions that are used to perform the text operations on the Salesforce fields, and one of them is the Salesforce SUBSTITUTE() function.

The Salesforce SUBSTITUTE() function, is used to substitute new text for old text in a text string. In simple words, the SUBSTITUTE function is used when we need to replace a specified substring within the text string with a new substring.

It is useful when you need to modify the data, clean the data, format the data, make corrections to the data, and make the data meaningful. In addition to this, you have to take care of the following considerations.

1. Case Sensitivity: The SUBSTITUTE function knows the difference between uppercase and lowercase characters i.e. it is case sensitive. For example, if you wrote “USA” and “usa” in the record it takes both as different. So, make sure you have entered the exact value in the old_text quotes that you want to replace.
2. Replace All Occurrences: The SUBSTITUTE function replaces each occurrence of all the old_text with the new_text value. It means that the function does not stop after replacing the first occurrence, it continues and replaces all old_text from the entire string.

Syntax of Salesforce SUBSTITUTE Function

Below is the syntax of the Salesforce SUBSTITUTE function:

SUBSTITUTE(text, old_text, new_text)

Here is a detailed description of the parameters:

• text: The text parameter specifies the text string or field with the text for which you want to replace all the occurrences of the old text and substitute the value.
• old_text: The old_text parameter specifies the substring or the old text that you want to replace within the text
• new_text: The new_text parameter specifies the substring or the new text that you want to replace with the old text.

Note one thing the function only accepts the text parameter If you pass any parameter other than the text field type such as number, currency, etc. it will produce an error.

Use Case of Salesforce SUBSTITUTE Function

Let’s see some practical examples of how to use the SUBSTITUTE function in Salesforce.

Example#1 Suppose you are working as a service executive in a multinational call center company that connects with various clients in different countries. You have observed that the salesforce database contains phone numbers in different formats such as dashes, dots, or space. To overcome this data inconsistency, you decided to substitute the one common separator i.e. space, and replace all the old separators. Here, is the formula for the scenario:

SUBSTITUTE(
    SUBSTITUTE(
        SUBSTITUTE(Phone, '-', ' '),
        '.', ' '),                  
    ' ', ' ')                      

Example#2 Suppose you are working in a global company as a sales manager and sell products to different countries. You have observed that the salesforce database contains the price field with the currency symbol ‘$’. Later on, you decided to remove the currency symbol $ and change it to the €. Here, is the formula for the scenario:

SUBSTITUTE(TEXT(Price__c) ,'$' , '€') 

With this, we have learned what is SUBSTITUTE function, its syntax, its use case, and also some considerations. Now, we will learn how to use the SUBSTITUTE Formula in Salesforce Lightning and Salesforce Classic one by one with an example.

Read Salesforce Case Insensitivity For text Field Type

How do I use the SUBSTITUTE Formula in Salesforce Lightning

The following are the steps to use the SUBSTITUTE formula function in Salesforce Lightning:

Step-1: Sign in to the Salesforce account in the Lightning Experience. If you are not on the main page of the lightning interface, click on the “Gear” icon at the right, and from the dropdown click on the “Setup” option (Setup for the current app).

Step-2: After being directed to the main setup for the current app in the lightning interface, click on the “Object Manager” option at the navigation bar.

Step-3: Once you click on the object manager, you will get the list of all the standard as well as the custom objects. Now decide the object on which you want to utilize the SUBSTITUTE function and search it in the “Quick Find” box and after finding it click on it.

For example: Imagine that you have decided to choose the “Contacts” object, so search for it and click on it.

Step-4: On the left side of the page section, you will get the “Fields & Relationships” option click on it. After that, click on the “New” button situated at the right side of the page as you have to create the formula field.

Step-5: Now you are on the step “Choose the Field Type”. Here, specify the type of information that the custom field will contain by choosing the appropriate field type.

Let’s suppose you need the “Formula” field type so click on the radio button adjacent to it, as you want to use the SUBSTITUTE formula. After that, click on the “Next” button and move to the next step.

Step-6: After you click on the next button, you will be on the “Choose Output Type” step. Enter the “Field Label” and “Field Name” and after that choose the formula return type. And once you do simply click on the “Next” button.

Let’s imagine you have entered the field name as “Offical Email” and when you click on the field name box it will be automatically populated. You have selected the formula return type as “TEXT” by clicking on the radio button in front of it as you want the resultant value in the text format.

Step-7: In this step, you are on the “Enter the Formula” step. Navigate towards the right side of the page in the “Functions” section. You will see the “All Functions Categories” dropdown, click on it.

Here you will see multiple functions categories that are available in Salesforce such as “Date&Time”, “Logical”, “Math”, “Text”, and “Advance”. Choose the function category that you require and then from the category select the function and click on it.

Let’s suppose, you need the SUBSTITUTE function and it is in the text category, so select the “TEXT” category, and after that click on the “SUBSTITUTE” function. Then, click on the “Insert Selected Function” button under the function category so that the function gets inserted into the advance formula subtab.

Replace the parameter with the appropriate merge field, functions, operators, etc. according to your scenario-specific needs.

Let’s take a scenario, imagine you want to add the official email address to each contact and the official email address is the same as the personal email address but with a slight difference. The difference is that instead of the personal domain name, the official address domain name is on the company name.

So, the requirement is only to change the domain name, For this the easiest way is the utilize the SUBSTITUTE function and replace the old domain name with the new domain name in the email address. Below is the formula for the, given requirement:

SUBSTITUTE(
    Email, 
    RIGHT(Email, LEN(Email) - FIND('@', Email)), 
    'creuztech.com'
)

Here, is a detailed explanation of the code:

• First, we use the SUBSTITUTE function, which replaces the original domain with the ‘creuztech.com’ and keeps the part before the @ symbol as it is.
• Then, we use the RIGHT function, to extract the original domain part of the email.
• After this, we use the LEN function, which is used to calculate the length of the total email address.
• Next to this, we use the FIND function, which is used to find the position of the @ symbol in the email address.
• In between the RIGHT function, we use the LEN(Email) – FIND(‘@’, Email), which is used to calculate the length of the domain by subtracting the position of the @ symbol from the total length of the email address.

Step-8: Once you enter the formula, it times to check whether it has some error or not, For this, you need to click on the “Check Syntax” button, and if there is an error it will show you the error message otherwise print the success message “No Syntax errors in merge fields or functions”.

If you want to add the “Description”, and “Help Text”, you can add them. Moreover, you can handle the blank fields. After that, click on the “Next” button.

Step-9: Upon clicking on the next button, you are redirected to the “Establish Field-Level Security” step. Choose the profiles to whom you want to grant field-level security access to editing fields. If you don’t select any profile, the field will be hidden from these specific profiles.

Let’s imagine, you want to grant access to all the profiles, so that is why you click on the checkbox next to the “Visibe”. Now, click on the “Next” button and move ahead.

Step-10: In this step, you are on the “Add to page layouts” page. Select the page layouts that will include the newly created custom formula type field. By default, all the page layouts are selected If you don’t want to include the field in the specific page layout, uncheck it and click on the “Save” button to finally save this.

Once the formula field is created, you can use the formula field. Let’s see an example:

• Open the “Contacts” object, create a new contact with the field “Email” and save it.
• After saving the case, move to the contacts details section and here you will see the “Offical Email” field showing the result of the SUBSTITUTE function.

Let’s deeply understand this example:

Imagine you have created a contact, with the email “bertha@fcof.net” and save it. So, for the email “bertha@fcof.net” the output of the official email is “bertha@creuztech.com”. This formula effectively substitutes the new value in place of the old value.

With this, we have learned the utilization of the SUBSTITUTE function in Salesforce Lightning. We shall now move and learn the steps of implementation of the SUBSTITUTE function in Salesforce Classic, with the help of an example.

Read LEN Function in Salesforce | How to calculate length of a text string in Salesforce

How do I use the SUBSTITUTE Formula in Salesforce Classic

The following are the steps to use the SUBSTITUTE formula function in Salesforce Classic:

Step-1: Login into the Salesforce in the Classic experience. If you are by default logged in to the Lightning interface, click on the “Profile” and from the dropdown click on the “Switch to Salesforce Classic”.

Step-2: Once you log in to the Salesforce Classic interface, click on the “Setup” option, to redirect to the Salesforce Classic setup.

Step-3: After reaching the classic setup, scroll down to the “Build” section on the left side of the page. Then click on the “Customize” dropdown select the Salesforce object on which you want to apply the SUBSTITUTION function and then click on the “Fields” option from the object dropdown.

Let’s assume you want to use the Salesforce SUBSTITUTION function, in the “Products” object.

Step-4: In this step, you are on the page where you will all the fields regarding the product object, and also you can create a new custom field for the product page. As your main agenda is to create a new field, scroll down to the “Product Custom Fields & Relationships” and click on the “New” button.

Step-5: Upon clicking on the new button, you are moved to the “Choose the Field Type” step. From here choose the field type best suited to your requirements. As your general requirement is the use of the SUBSTITUTION function, click on the radio button in front of the “Formula” field type. After this click on the “Next” button.

Step-6: In this step, you are on the “Choose Output Type” page. Fill out the “Field Label” and “Field Name”. Also, select the return type of the formula field by scrolling down to the “Formula Return Type” section. Then, click on the “Next” button to go to the next step.

Let’s imagine, you have entered the field label named “Updated Product SKU” and once you click on the field name box it gets automatically filled. Now, choose the formula return type as “Text” as your main goal is to substitute the new value in place of the old value.

Step-7: Once you click on the next, you move to the “Enter Formula” step. Now, move towards the right side of the page to the “Functions” section. After that, click on the “All Functions Categories” dropdown, you will get all the categories of the functions that are in Salesforce.

From these categories, choose the category in which your function resides and then find your function, click on it, and then click on the “Insert Selected Function” under the function box.

The function gets inserted into the advance formula box you can replace the function parameter with the appropriate text, fields, etc. as per the requirement.

Let’s imagine you work as a product manager in the company and the SKU name of the product gets changed after many years. Now, you get a task to update the product SKU name. So, for this, you have decided to use the Salesforce SUBSTITUTE function as this function can easily do this task.

To add this function to the advance formula subtab, you choose the “TEXT” category as you know that the function is of text type and after that, you find and click on the “SUBSTITUTE” function of salesforce.

Once the function is inserted you replace the parameter with these fields and values and create your own formula which is as given below:

IF(
    CONTAINS( StockKeepingUnit , 'LT-'),  
    SUBSTITUTE( StockKeepingUnit , 'LT-', 'ELT-'), 
    IF(
        CONTAINS( StockKeepingUnit , 'SP-'),  
        SUBSTITUTE( StockKeepingUnit , 'SP-', 'ESP-'), 
        'SKU Not Assigned'
    )
)

Here is a detailed explanation of the code:

• We first, use the IF function that checks whether the Product SKU field starts with the string character ‘LT’:
  • If the statement is True, replace the ‘LT’ with the ‘ELT’ in the Product SKU field by using the SUBSTITUTION function.
  • If the statement is False, move to the second IF function statement.
• Then we use the second time, IF function that checks whether the Product SKU field starts with the string character ‘SP’:
  • If the statement is True, replace the ‘SP’ with the ‘ESP’ in the Product SKU filed by using the SUBSTITUTION function.
  • If the statement is False, print ‘SKU Not Assigned’.

Step-8: After you enter the formula, click on the “Check Syntax” button to check whether the above-entered formula is correct or has some errors. You will get the message “No syntax errors in merge fields or functions” If the formula is correct, but if the formula has some errors, you will get the error message.

Moreover, if you want, you can enter “Description” and “Help Text”. In addition, if your formula references any number, currency, or percent fields, specify what happens to the formula output when their values are blank Either you want to treat blank fields as zeroes or treat blank fields as blanks.

After that click on the “Next” button and move to the next step.

Step-9: In this step, you are on the “Establish Field-Level Security” page. Select the profiles to which you want to grant edit access to this via field-level security. The field will be hidden from all profiles if you do not add it to field-level security.

By default, some of the field-level security profiles are selected, if you want to select or unselect any other profile, you can do it with the help of the checkbox that is given next to the profile. Then, click on the “Next” button and move to the next step.

Let’s imagine you want to select all the profiles, so click on the “Visible” checkbox.

Step-10: After you click on the next, you are directed to the “Add to Page Layouts” step. Select the page layouts that should include the field. The field will be added as the last field in the first 2 column section of these page layouts. The field will not appear on any pages. If you do not select a layout.

By default, all the page layout names are selected, if you want to show this field only on some of the page layouts, select the checkbox of the unwanted page layout. When finished, click the “Save” button to complete the process of creating the formula field in Salesforce Classic with the SUBSTITUTION function.

Step-11: Once the formula field is created, you can use the formula field. Let’s see an example:

• Open the “Product” object, create a new product with “Product SKU” and save it.
• Once you save it, move to the product details page, and you will see the “Updated Product SKU” field showing the result of the SUBSTITUTION function.

Let’s understand the example in detail.

Suppose, you have created a product with the Product SKU “SP-9894DF45” and saved it. Now, the formula will execute and show the updated Product SKU “ESP-9894DF45”. It means that the formula effectively substitutes the new value in the place of the old value.

With this, we have learned how to use the SUBSTITUTION function to substitute new text for old text in a text string in Salesforce Classic with an example.

Conclusion

In a nutshell, we have learned about the Salesforce SUBSTITUTION function, it is used to replace the new text for old text in a text string. Moreover, we have learned the syntax, its practical examples, some use cases, and considerations.

Furthermore, we have discussed the process of how to use the SUBSTITUTION function in Salesforce Lightning and Salesforce Classic.

You may like to read the following articles:

Salesforce REVERSE Function | How to reverse string in Salesforce