SUBSTITUTE formula is used to replace specific text in a string with new text. It is commonly used to replace all occurrences of a certain word or character within a larger string. The formula has the option to specify which occurrence to replace, or it will replace all occurrences by default.
- How to use
- Examples of using
SUBSTITUTEformula not working?
- Similar formulas to
SUBSTITUTE formula with the syntax shown below, it has 3 required parameters and 1 optional parameter:
- text_to_search (required):
The text or cell reference containing the text that you want to make replacements in.
- search_for (required):
The text or cell reference containing the text that you want to replace.
- replace_with (required):
The text that you want to replace the search text with.
- occurrence_number (optional):
Optional. The occurrence number of the search text that you want to replace. If omitted, all occurrences will be replaced.
ExamplesHere are a few example use cases that explain how to use the
SUBSTITUTE formula in Google Sheets.
Replace a single occurrence of text
SUBSTITUTE to replace only the first occurrence of a specific word or character in a string.
Replace all occurrences of text
SUBSTITUTE to replace all occurrences of a specific word or character in a string.
Remove specific text from a string
SUBSTITUTE to remove a specific word or character from a string by replacing it with an empty string.
Replace text based on a condition
SUBSTITUTE within an
IF statement to replace text in a string based on a condition or criteria.
SUBSTITUTE not working? Here are some common mistakes people make when using the
SUBSTITUTE Google Sheets Formula:
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 -
The following functions are similar to
SUBSTITUTE or are often used with it in a formula:
REPLACEformula 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.
REGEXREPLACEformula 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.
LEFTformula 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.
RIGHTformula 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.
You can learn more about the
SUBSTITUTE Google Sheets function on Google Support.