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.
- How to use
- Examples of using
REPLACEformula not working?
- Similar formulas to
REPLACE formula with the syntax shown below, it has 4 required parameters:
- text (required):
The text string containing the characters to be replaced.
- position (required):
The starting position of the characters to be replaced. Must be a positive integer.
- length (required):
The number of characters to be replaced. Must be a positive integer.
- new_text (required):
The new text to replace the specified characters with.
ExamplesHere are a few example use cases that explain how to use the
REPLACEformula in Google Sheets.
Remove a specific character from a text string
By using the
REPLACE formula, you can remove a specific character from a text string by specifying the position and length of the character to be removed, and replacing it with an empty string.
Replace a word with another word in a text string
You can use the
REPLACE formula to replace a specific word in a text string with another word by specifying the position and length of the word to be replaced, and replacing it with the new word.
Remove a prefix from a text string
If you have a text string with a prefix that you want to remove, you can use the
REPLACE formula to remove it by specifying the position and length of the prefix, and replacing it with an empty string.
REPLACEnot working? Here are some common mistakes people make when using the
REPLACEGoogle Sheets Formula:
Incorrect position or length
Make sure to specify the correct position and length of the characters to be replaced. Count starts from 1.
Missing new text
Make sure to specify the new text to replace the specified characters in the original text string.
The following functions are similar to
REPLACE or are often used with it in a formula:
SUBSTITUTEformula 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.
FINDformula in Google Sheets searches for a specific text within another text and returns the position of the first occurrence of the text. This formula is often used to extract a substring from a larger string or to check if a certain text exists within another text.
LENformula in Google Sheets is used to count the number of characters in a given text string. It is commonly used to check the length of text inputs, or to find the length of a cell value or range.
You can learn more about the
REPLACE Google Sheets function on Google Support.