Text
REPLACE
Definition
REPLACE returns text with a given number of characters swapped for new characters, starting at a position you choose.
Syntax
=REPLACE(old_text, start_num, num_chars, new_text)
Arguments
| Argument | Meaning |
|---|---|
old_text |
The original text, or the cell holding it. |
start_num |
The first character to replace, counting 1 as the start of the text. |
num_chars |
How many characters to remove from that starting point. |
new_text |
The characters to put in their place. Text must sit in double quotes. |
Worked example
A yard recodes labels by swapping three letters from position 2. Labels are in A2:A4.
| A: Label |
|---|
| BOARD |
| HOUSE |
| CRANE |
Formula: =REPLACE(A2,2,3,"IN")
Result: BIND. Positions 2 to 4 of BOARD are OAR, which become IN. Filled down, the formula gives BIND, HINE, and CINE.
Practice
Cell A2 holds ABCDE. In B2, write a formula that replaces 3 characters starting at position 2 with X.
- In B2, write a REPLACE formula that replaces 3 characters of A2 starting at position 2 with X.
- B2 should show A2 after replacing 3 characters starting at 2 with X.
Step 1 of 2
Enter =REPLACE(A2,2,3,"X") in B2.
Replacing 3 characters starting at 2 in ABCDE with X gives AXE.
Formulas here use English function names, such as SUM, and a period as the decimal separator, in every language.
Loading the sheet…
B2 uses REPLACE.
B2 now shows AXE.
Related functions
EXACT can check the new text against a target. LEFT can take a prefix from the result.
Common mistake
Counting start_num from 0: the first character is position 1, so REPLACE(A2,0,3,"X") returns #VALUE!.