REPLACE

Formulas / REPLACE
To replace characters in a string by location.
==REPLACE(old_text,start_num,num_chars,new_text)
  • old_text - the text that needs some characters replaced
  • start_num - the position of the old_text character to replace with new_text
  • num_chars - the number of characters that should be replaced in old_text with new_text
  • new_text - the string that is used to replace text within old_text

Examples

    The REPLACE function can be used to replace characters in a string. For example, to replace the word "C" with the word "D" in the string "C:\docs", you would use the following formula:

  • =REPLACE("C:\docs",1,1,"D")

    This would return the string "D:\docs".

    • The REPLACE function can also be used to replace a substring in a string. For example, to replace the substring "123" with the substring "456" in the string "XYZ123", you would use the following formula:

  • =REPLACE("XYZ123",4,3,"456")

    This would return the string "XYZ456".

    • The REPLACE function can also be used to remove characters from a string. In this example, the REPLACE function returns "google.com" by removing the first four characters from "www.google.com":

  • =REPLACE("www.google.com",1,4,"")

Summary

The REPLACE function is used to replace a text string with another text string.

  • The REPLACE function is useful if the location of the text to replace is known or can be determined easily.
  • Setting new_text to an empty string "" removes text.
  • A #VALUE! error is returned if start_num is not positive or num_chars is not positive.
  • The REPLACE function returns text when used on numbers.

Frequently Asked Questions

What is the REPLACE function?
The REPLACE function replaces text in old_text with new_text.
What is the difference between the REPLACE and REPLACEB functions?
REPLACE is intended for use with languages that use the single-byte character set (SBCS), while REPLACEB is intended for use with languages that use the double-byte character set (DBCS).
Are the REPLACE and REPLACEB functions available in all languages?
No, the REPLACE and REPLACEB functions may not be available in all languages.
How does REPLACE handle different language settings?
REPLACE counts each character as 1, no matter what language setting you choose.
What are the arguments for the REPLACE function?
There are four arguments for the REPLACE function: old_text, start_num, num_chars, new_text. Every argument is required.

Related Functions

Sourcetable Logo

Work smarter

Al is here to help. Leverage the latest models to
analyze spreadsheets, enrich data, and create reports.

Drop CSV