SUBSTITUTE

• How to use SUBSTITUTE formula?
• Examples of using SUBSTITUTE formula
• SUBSTITUTE formula not working?
• Similar formulas to SUBSTITUTE

Usage

Use the SUBSTITUTE formula with the syntax shown below, it has 3 required parameters and 1 optional parameter:

=SUBSTITUTE(text_to_search, search_for, replace_with, [occurrence_number])

1. text_to_search (required):
   The text or cell reference containing the text that you want to make replacements in.
2. search_for (required):
   The text or cell reference containing the text that you want to replace.
3. replace_with (required):
   The text that you want to replace the search text with.
4. occurrence_number (optional):
   Optional. The occurrence number of the search text that you want to replace. If omitted, all occurrences will be replaced.

Examples

Replace a single occurrence of text

Use SUBSTITUTE to replace only the first occurrence of a specific word or character in a string.

Replace all occurrences of text

Use SUBSTITUTE to replace all occurrences of a specific word or character in a string.

Remove specific text from a string

Use SUBSTITUTE to remove a specific word or character from a string by replacing it with an empty string.

Replace text based on a condition

Use SUBSTITUTE within an IF statement to replace text in a string based on a condition or criteria.

Common Mistakes

Forgetting to include the text to search parameter

One common mistake is forgetting to include the text to search parameter. Double check that the parameter is included and is referencing the correct cell or text.

Using the wrong occurrence number

If you want to replace a specific occurrence of the search text, make sure you are using the correct occurrence number. If you are replacing all occurrences, make sure the occurrence number parameter is omitted.

Not escaping special characters

If you are replacing a special character, make sure you are escaping it properly. For example, if you want to replace a backslash, you need to use two backslashes to escape it - =SUBSTITUTE(A1,"\\","/").

Related Formulas

The following functions are similar to SUBSTITUTE or are often used with it in a formula:

• REPLACE

  The REPLACE formula is used to replace a specified number of characters, starting from a specified position, in a text string with new text. It is commonly used to replace or remove specific characters or words from a text string.

• REGEXREPLACE

  The REGEXREPLACE formula is used to replace a piece of text that matches a regular expression with a new piece of text. This formula is useful when working with text data that needs to be cleaned or manipulated based on specific patterns or rules.

• LEFT

  The LEFT formula is used to extract a specific number of characters from the beginning of a text string. It is most commonly used to extract the first name or last name from a full name or to extract a date from a text string.

• RIGHT

  The RIGHT formula in Google Sheets returns the rightmost characters from a string, based on the number of characters specified. It is commonly used to extract a certain number of characters from the end of a string. The formula takes two arguments: the string from which to extract the characters, and the number of characters to extract.

Learn More

You can learn more about the SUBSTITUTE Google Sheets function on Google Support.