Salesforce SUBSTITUTE() Function: Syntax, Examples, and Common Mistakes

The Salesforce SUBSTITUTE() function is one of the most useful text functions in formulas. It allows you to find specific text within a string and replace it with different text.

You can use SUBSTITUTE() in many real-world scenarios, such as formatting phone numbers, standardizing country names, updating email domains after a company merger, or cleaning data before displaying it in Salesforce records.

In this tutorial, you’ll learn the syntax of the SUBSTITUTE() function, practical examples that you can use in your Salesforce org, common mistakes to avoid, and the difference between SUBSTITUTE() and REPLACE().

What Is the SUBSTITUTE() Function in Salesforce?

The Salesforce SUBSTITUTE() function replaces specific text within a string. It is one of the most commonly used text functions in Salesforce formulas because it helps you modify text without writing Apex code.

You can use the SUBSTITUTE() function in Formula Fields, Flow Formulas, Validation Rules, and other places where Salesforce formulas are supported.

Common use cases include:

• Cleaning imported data
• Standardizing values across records
• Removing unwanted characters from text
• Updating text formats
• Preparing data for integrations with external systems

Syntax – SUBSTITUTE() Function in Salesforce

SUBSTITUTE(text, old_text, new_text)

Parameters

• text – The text value or field that contains the text you want to modify.
• old_text – The text you want Salesforce to find and replace.
• new_text – The text that will replace the old text.

Important Points to Remember

• SUBSTITUTE() is case-sensitive, so “USA” and “usa” are treated as different values.
• It replaces all occurrences of the specified text.
• If Salesforce cannot find the text specified in old_text, it returns the original value unchanged.
• The function can be used in Formula Fields, Flow Formulas, Validation Rules, and most other formula-supported areas in Salesforce.

Because SUBSTITUTE() replaces every matching occurrence, it is a great option when you need to update text consistently across a field or formula result.

SUBSTITUTE Syntax and Parameters (With Detailed Examples)

Let’s understand each parameter of the SUBSTITUTE() function with simple Salesforce examples.

Complete Formula Structure

SUBSTITUTE(Account.Name, "USA", "United States")

This formula searches for the text “USA” in the Account Name and replaces it with “United States”.

For example, if the Account Name is ABC USA Ltd, the result will be ABC United States Ltd.

Parameter 1: text (The Text to Search)

The first parameter is the text value that Salesforce will look for to replace.

It can be:

• A field value such as Phone, ShippingCountry, Description, or Email
• A text string such as “Hello World.”
• A formula that returns text
• The result of another text function

Example Using a Field

SUBSTITUTE(Phone, "-", "")

This formula removes all dashes from the Phone field.

Example Using a Text String

SUBSTITUTE("Hello-World", "-", " ")

Result:

Hello World

Parameter 2: old_text (Text to Replace)

The second parameter specifies the text that Salesforce should find and replace.

When using a fixed text value, it must be enclosed in double quotes.

Examples

"-"
"USA"
"@gmail.com"
" "
"("
")"
",,"

Common Mistake

Incorrect:

SUBSTITUTE(Phone, -, "")

Correct:

SUBSTITUTE(Phone, "-", "")

The dash must be enclosed in double quotes because it is a text value.

Parameter 3: new_text (Replacement Text)

The third parameter specifies the new text that will replace the old text.

You can use:

• A text value
• Another text field
• A formula result
• Empty quotes (” “) to remove text completely

Example: Remove Dashes

SUBSTITUTE(Phone, "-", "")

If the Phone field contains:

123-456-7890

The result will be:

1234567890

Because the dash is replaced with nothing.

Example: Replace a Country Name

SUBSTITUTE(ShippingCountry, "USA", "United States")

If the value is:

USA

The result will be:

United States

Example: Replace an Email Domain

SUBSTITUTE(Email, "@oldcompany.com", "@newcompany.com")

If the email address is:

john@oldcompany.com

The result will be:

john@newcompany.com

By combining these three parameters, you can easily clean, standardize, and format text values in Salesforce formulas without writing Apex code.

When to Use the Salesforce SUBSTITUTE() Function

The SUBSTITUTE() function is a good choice when you need to replace specific text within a text string.

Use SUBSTITUTE() when you want to:

• Replace one text value with another
• Remove unwanted characters by replacing them with empty quotes (” “)
• Standardize values across records
• Clean data in Formula Fields or Flow Formulas
• Format data before sending it to external systems

Real-World Salesforce Use Cases

Below, I will explain some real-world use cases to help you better understand the SUBSTITUTE() function.

1. Cleaning Phone Numbers

Many integrations require phone numbers without special characters.

For example:

SUBSTITUTE(Phone, "-", "")

This removes dashes from phone numbers before sending data to an external application.

2. Standardizing Country Names

Different users may enter country names in different formats, such as:

• USA
• US
• U.S.

You can use SUBSTITUTE() to convert these values into a single format, making Salesforce reports more accurate and consistent.

3. Updating Email Domains

When a company changes its email domain, SUBSTITUTE() can help update email addresses.

Example:

SUBSTITUTE(Email, "@oldcompany.com", "@newcompany.com")

4. Removing Duplicate Characters

Sometimes imported data contains unwanted duplicate characters.

For example:

New York,,NY,10001

can be cleaned to:

New York,NY,10001

using the SUBSTITUTE() function.

5. Standardizing Product Names

If some records use “Coupon” and others use “Discount”, you can replace the old term with the new one to keep data consistent.

6. Cleaning Address Data

Address values often contain different abbreviations, such as:

• Street
• St.
• ST

You can standardize these values using SUBSTITUTE() to maintain consistent address formatting.

When Not to Use the SUBSTITUTE() Function

SUBSTITUTE() is useful for simple text replacement, but it is not the best option for every scenario.

Avoid using SUBSTITUTE() when:

1. You Need to Replace Text at a Specific Position
   • SUBSTITUTE() searches for matching text values. If you need to replace characters based on their position in the string, use the REPLACE() function instead.
2. You Need to Replace Only the First Occurrence
   • SUBSTITUTE() replaces all matching occurrences. If you need more control over which occurrence is replaced, you may need a combination of functions such as FIND(), LEFT(), MID(), and RIGHT().
3. You Need Position-Based Logic
   • For advanced text manipulation based on where text appears in a string, use functions like FIND(), LEFT(), RIGHT(), or MID().
4. You Are Performing Complex Data Transformations
   • If the logic becomes too complex, consider using Flow, Apex, or another automation tool instead of a large formula.

SUBSTITUTE() vs REPLACE() Functions in Salesforce

A simple rule to remember:

• Use SUBSTITUTE() when you want to replace specific text.
• Use REPLACE() when you want to replace characters based on their position within a text string.

In most Salesforce text-cleanup scenarios, SUBSTITUTE() is the easier and more commonly used option.

Real Salesforce Examples (USA-Focused with Step-by-Step Explanations)

Here are practical examples I’ve used in actual Salesforce orgs across the United States. I’m using USA-specific names, formats, and business scenarios so these match what you’ll see in your organization.

Example 1: Replace Email Domain After Company Acquisition

Business scenario: Your company acquired TechStart Inc. All employees with @techstart.com email addresses need to appear as @yourcompany.com internally for reporting.

Field: Email on Contact object

Formula:

SUBSTITUTE(Email, "@techstart.com", "@yourcompany.com")

Test data:

• Before: jennifer.martinez@techstart.com
• After: jennifer.martinez@yourcompany.com
• Before: john.smith@yourcompany.com
• After: john.smith@yourcompany.com (unchanged)

This only changes the domain part, not the username before the @.

Follow these steps to use the SUBSTITUTE() formula function in Salesforce Lightning:

Step 1: Open Object Manager

1. Log in to Salesforce Lightning.
2. Click Setup (⚙️ Gear Icon).
3. Select Object Manager.

Step 2: Select the Object

1. Search for and open the object where you want to create the formula field.
2. Example: Contact object.

Step 3: Create a Formula Field

1. Click Fields & Relationships.
2. Click New.
3. Select Formula as the field type.
4. Click Next.

Step 4: Configure the Formula Field

1. Enter the Field Label.
   • Example: Official Email
2. Select Text as the Formula Return Type.
3. Click Next.

Step 5: Enter the SUBSTITUTE Formula

In the Formula Editor, enter the following formula:

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

This formula replaces the existing email domain with creuztech.com while keeping the username part unchanged.

Step 6: Validate the Formula

1. Click Check Syntax.
2. Verify that Salesforce displays:
   “No syntax errors in merge fields or functions.”

Step 7: Configure Field Security

1. Select the profiles that should have access to this field.
2. Click Next.

Step 8: Add to Page Layouts

1. Select the page layouts where the field should appear.
2. Click Save.

Step 9: Test the Formula

Create or open a Contact record with an email address such as:

bertha@fcof.net

Output:

bertha@creuztech.com

The SUBSTITUTE() function replaces the old domain with the new domain and displays the updated value in the formula field.

Example 2: Replace Country Abbreviation in Salesforce

Business Scenario:
Your users enter USA in the Shipping Country field, but an external shipping system requires the full country name United States. To keep your data consistent and avoid integration issues, you can use the SUBSTITUTE() function.

Field: ShippingCountry__c

Formula:

SUBSTITUTE(ShippingCountry__c, "USA", "United States")

Example Results

As you can see, Canada remains unchanged because it does not contain the text USA.

Also, notice that the USA remains unchanged. This happens because the SUBSTITUTE() function is case-sensitive. Salesforce treats USA, usa, and Usa as different values.

Handling Mixed-Case Values

If your Salesforce data contains values such as USA, usa, or Usa, you can first convert the field value to uppercase and then perform the replacement.

SUBSTITUTE(
    UPPER(ShippingCountry__c),
    "USA",
    "United States"
)

Example Results

This approach helps standardize country names and ensures consistent data for reports, integrations, and business processes.

Example 3: Clean Phone Numbers by Removing Formatting Characters

Business Scenario:
You need to send phone numbers from Salesforce to an external system that only accepts digits. However, users enter phone numbers in different formats using parentheses, dashes, and spaces.

Examples of Phone Number Formats:

(555) 123-4567
555-123-4567
(555)123-4567
555 123 4567

Required Output:

5551234567

Formula

SUBSTITUTE(
    SUBSTITUTE(
        SUBSTITUTE(
            SUBSTITUTE(Phone__c, "(", ""),
            ")", ""
        ),
        "-",
        ""
    ),
    " ",
    ""
)

How the Formula Works

The formula uses multiple SUBSTITUTE() functions to remove unwanted characters one by one:

1. Removes all opening parentheses (
2. Removes all closing parentheses )
3. Removes all dashes -
4. Removes all spaces

After all replacements are completed, only the phone number digits remain.

Example Results

Additional Enhancement

If your users also enter phone numbers with periods, such as:

555.123.4567

You can remove periods as well by adding one more SUBSTITUTE() function:

SUBSTITUTE(
    SUBSTITUTE(
        SUBSTITUTE(
            SUBSTITUTE(
                SUBSTITUTE(Phone__c, "(", ""),
                ")", ""
            ),
            "-",
            ""
        ),
        " ",
        ""
    ),
    ".",
    ""
)

Result:

5551234567

This is a common Salesforce use case when preparing phone numbers for integrations, SMS platforms, and third-party applications that require numbers in a standard format.

SUBSTITUTE vs REPLACE: Which Should You Use?

Salesforce has both SUBSTITUTE and REPLACE functions, and they’re not interchangeable. Here’s exactly how to choose.

Salesforce’s official guidance: If the changes you want to apply to a string don’t require a regular expression, use SUBSTITUTE.

Practical decision rule:

• Use SUBSTITUTE when you know the exact text to replace
• Use REPLACE when you need to match patterns like “all emails,” “all phone numbers,” or “any digit.”

Example where REPLACE is better:
If you want to remove all numbers from a string, REPLACE with regex is easier than trying to SUBSTITUTE each digit 0-9.

Common SUBSTITUTE() Errors in Salesforce and How to Fix Them

The SUBSTITUTE() function is easy to use, but there are a few common mistakes that Salesforce Admins and Developers often run into. Here are the most common issues and their solutions.

Error 1: Case Sensitivity

Problem

The SUBSTITUTE() function is case-sensitive. It only replaces text that exactly matches the specified value.

SUBSTITUTE(Country__c, "usa", "United States")

If the field contains USA, nothing will be replaced because usa and USA are different values.

Solution

Convert the field value to a consistent case before performing the replacement.

SUBSTITUTE(
    UPPER(Country__c),
    "USA",
    "United States"
)

You can also handle multiple cases separately if needed.

SUBSTITUTE(
    SUBSTITUTE(Country__c, "USA", "United States"),
    "usa",
    "United States"
)

Error 2: Expecting Only the First Occurrence to Be Replaced

Problem

SUBSTITUTE() replaces all matching occurrences in the text.

SUBSTITUTE(Description, "-", "")

If the value is:

A-B-C-D

The result will be:

ABCD

Solution

If you need to replace only the first occurrence, use text functions such as FIND(), LEFT(), MID(), and RIGHT() to rebuild the string.

Example:

LEFT(Description, FIND("-", Description) - 1) &
MID(
    Description,
    FIND("-", Description) + 1,
    LEN(Description)
)

For more complex requirements, consider using a Flow or Apex.

Error 3: Missing Double Quotes Around Text Values

Problem

SUBSTITUTE(Phone__c, -, " ")

This causes a formula syntax error because Salesforce treats - as an operator rather than a text value.

Solution

Always place literal text inside double quotes.

SUBSTITUTE(Phone__c, "-", "")

Error 4: Using SUBSTITUTE() with Non-Text Fields

Problem

SUBSTITUTE() works with text values. If you use Number, Currency, Date, or Date/Time fields directly, Salesforce may return an error.

Solution

Convert the value to text first using the TEXT() function.

Number Example

SUBSTITUTE(TEXT(AccountNumber__c), "0", "")

Date Example

SUBSTITUTE(TEXT(CreatedDate), "2024", "2025")

Error 5: Creating Hard-to-Read Nested Formulas

Problem

Large nested formulas become difficult to read and maintain.

SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Phone__c,"(",""),")",""),"-","")

Solution

Format the formula using line breaks and indentation.

SUBSTITUTE(
    SUBSTITUTE(
        SUBSTITUTE(
            Phone__c,
            "(",
            ""
        ),
        ")",
        ""
    ),
    "-",
    ""
)

The logic remains the same, but the formula is much easier to understand and troubleshoot.

Error 6: Formula Size Limits

Problem

Using too many nested SUBSTITUTE() functions can make your formula large and difficult to maintain. Salesforce also enforces formula size limits.

Solution

If your formula becomes too complex:

• Split the logic into multiple formula fields
• Use Formula Resources in Flow
• Move complex processing to Apex when necessary

Keeping formulas simple improves readability and makes future maintenance easier.

Salesforce SUBSTITUTE() Function Limitations and Best Practices

Before using the SUBSTITUTE() function in your Salesforce formulas, it’s important to understand its limitations and follow some best practices. This will help you build formulas that are easier to maintain and perform well as your data grows.

Limitations of the SUBSTITUTE() Function

While SUBSTITUTE() is very useful for text replacement, it has a few limitations.

1. Case-Sensitive Matching

SUBSTITUTE() is case-sensitive.

For example:

SUBSTITUTE(Country__c, "USA", "United States")

This formula will replace USA, but it will not replace usa or Usa.

2. Replaces All Occurrences

SUBSTITUTE() replaces every matching occurrence in the text.

For example:

SUBSTITUTE("A-B-C-D", "-", "")

Result:

ABCD

You cannot use SUBSTITUTE() to replace only the first occurrence.

3. No Regular Expression Support

SUBSTITUTE() only replaces exact text matches.

If you need advanced pattern-based matching, Salesforce formulas do not provide native regular expression functions. In those cases, consider using Flow, Apex, or other automation approaches.

4. Formula Size Limits

Very large formulas with many nested functions can hit Salesforce formula size limits and become difficult to maintain.

If your formula becomes too complex, consider breaking the logic into multiple formula fields or moving the logic to Flow or Apex.

5. Works Best with Text Values

SUBSTITUTE() is designed for text.

If you’re working with Number, Currency, Date, or Date/Time fields, convert them to text first using the TEXT() function.

Example:

SUBSTITUTE(TEXT(AccountNumber__c), "0", "")

Salesforce SUBSTITUTE() Function Best Practices

1. Keep Formulas Easy to Read

When using multiple nested SUBSTITUTE() functions, format the formula properly.

Instead of:

SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Phone__c,"("," "),")"," "),"-"," ")

Use:

SUBSTITUTE(
    SUBSTITUTE(
        SUBSTITUTE(
            Phone__c,
            "(",
            ""
        ),
        ")",
        ""
    ),
    "-",
    ""
)

This makes troubleshooting much easier later.

2. Test with Real Salesforce Data

Before deploying a formula:

• Test with valid values
• Test with blank fields
• Test with unexpected characters
• Test with long text values
• Test records where the text being replaced does not exist

Testing real-world scenarios helps prevent issues in production.

3. Break Complex Logic into Smaller Steps

If a formula contains many nested functions, consider splitting the logic into multiple formula fields.

Example:

• Phone_Cleaned__c – Removes formatting characters
• Phone_Validated__c – Checks the phone number length
• Phone_Final__c – Adds a country code if required

Smaller formulas are easier to understand and maintain.

4. Document Your Formula Logic

Add a clear description to the formula field.

Your description should explain:

• What the formula does
• Why is SUBSTITUTE() being used
• Any important assumptions
• Special handling, such as case sensitivity

Good documentation helps future admins understand the purpose of the formula.

5. Consider Performance for Complex Formulas

A simple SUBSTITUTE() function has minimal impact on performance. However, very large and deeply nested formulas can become harder to maintain and may affect page performance when used extensively.

If you have complex processing requirements on high-volume objects such as Account, Contact, or Opportunity:

• Test performance in a sandbox
• Review page load times
• Consider Flow or Apex for advanced processing

6. Use SUBSTITUTE() for Text Cleanup

SUBSTITUTE() is best used for:

• Removing unwanted characters
• Standardizing values
• Updating text formats
• Preparing data for integrations

If your requirement involves complex business logic or building entirely new values, other formula functions, Flow, or Apex may be a better choice.

Frequently Asked Questions

Final Thoughts

The Salesforce SUBSTITUTE() function is a simple yet powerful text function that helps you replace specific text in a string. It is commonly used to clean data, standardize values, remove unwanted characters, and prepare data for integrations.

Whether you need to update country names, clean phone numbers, standardize email domains, or remove extra characters from imported data, SUBSTITUTE() can handle these tasks without requiring Apex code.

The most important thing to remember is that SUBSTITUTE() works with exact text matches and is case-sensitive. It is best suited for simple text replacement scenarios where you know exactly what text needs to be replaced.

You may like to read:

• Salesforce Formula Field to Calculate Age
• Salesforce Picklist Default Value Formula
• Salesforce Checkbox Formula
• Salesforce Formula Add Year to Date
• Salesforce Formula Add Months to Date

Bijay Kumar

I am Bijay Kumar, the founder of SalesforceFAQs.com. Having over 10 years of experience working in salesforce technologies for clients across the world (Canada, Australia, United States, United Kingdom, New Zealand, etc.). I am a certified salesforce administrator and expert with experience in developing salesforce applications and projects. My goal is to make it easy for people to learn and use salesforce technologies by providing simple and easy-to-understand solutions. Check out the complete profile on About us.