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
To remove text from a cell by position, you can use the REPLACE function.
In the example shown, the formula in C6 is:
=REPLACE(B6,1,24,"")
How this formula works
The replace function lets you replace text...
To remove text from a cell when the text is at a variable position, you can use a formula based on the REPLACE function, with help from the FIND function.
In the example shown, the formula in C6 is:
=REPLACE(B6,1,...
To remove the first character in a cell, you can use the REPLACE function. In the example shown, the formula in D5 is:
=REPLACE(A1,1,1,"")
How this formula works
This formula uses the REPLACE function to...
Related functions
Good links
