Excel REPLACE Function
Summary
The Excel REPLACE function replaces characters specified by location in a given text string with another text string. For example =REPLACE("XYZ123",4,3,"456") returns "XYZ456".
Purpose
Replace text based on location
Return value
The altered text.
Syntax
=REPLACE (old_text, start_num, num_chars, new_text)
Arguments
- old_text - The text to replace.
- start_num - The starting location in the text to search.
- num_chars - The number of characters to replace.
- new_text - The text to replace old_text with.
Usage notes
The REPLACE function is useful for replacing text at a known location in a given string. For example, the following formula replaces 4 characters starting at the 3rd character:
=REPLACE("XYZ123",4,3,"456") // returns "XYZ456"
You can use REPLACE to remove text by specifying an empty string ("") as new text. In the formula below, we use REPLACE to remove the first character:
=REPLACE("XYZ",1,1,"") // returns "YZ"
Excel has several functions that can be used to replace text. Below is a brief summary. Also see linked examples below.
- Use the REPLACE function when you want to replace text based on a known location.
- Use FIND or SEARCH to find and replace text when the location is not known in advance.
- Use SUBSTITUTE to replace one or more instances of text based on content only.
REPLACE formula examples
This formula uses the REPLACE function to replace the first character in a cell with an empty string (""). The arguments for REPLACE are configured as follows:
old_text is the original value from column B
start_num...
The replace function lets you replace text based on its location and length. In this case, we want to strip off the drive and path, and leave only the document name.The length of this part of the string (text) is 24...
The REPLACE function will replace text by position. You can use REPLACE to remove text by providing an empty string ("") for the "new_text" argument.
In this case, we want to remove the labels that appear inside text...
Related functions
