REPLACE

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

Usage

Use the REPLACE formula with the syntax shown below, it has 4 required parameters:

=REPLACE(text, position, length, new_text)

1. text (required):
   The text string containing the characters to be replaced.
2. position (required):
   The starting position of the characters to be replaced. Must be a positive integer.
3. length (required):
   The number of characters to be replaced. Must be a positive integer.
4. new_text (required):
   The new text to replace the specified characters with.

Examples

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.

Common Mistakes

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.

Related Formulas

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

• SUBSTITUTE

  The 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.

• FIND

  The FIND formula 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.

• LEN

  The LEN formula 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.

Learn More

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