Text
Replacing one word with another
Definition
Replacing one word with another in a cell means giving SUBSTITUTE the text, the old word, and the new word.
Syntax
=SUBSTITUTE(text, old_text, new_text)
Arguments
| Argument | Meaning |
|---|---|
text |
The cell that holds the phrase to edit. |
old_text |
The word to find, in double quotes, for example "old". |
new_text |
The word to put in its place, for example "new". |
Worked example
A route list stores labels in B2:B4. The first label still says old instead of new.
| A: Run | B: Label |
|---|---|
| 1 | old code |
| 2 | old gate |
| 3 | dock path |
Formula: =SUBSTITUTE(B2,"old","new")
Result: new code. The word old is replaced, and the rest of the label stays.
Practice
Cell A2 holds red red. In B2, write a formula that replaces the word red with blue.
- In B2, write a SUBSTITUTE formula that replaces red with blue in A2.
- B2 should show A2 after replacing red with blue.
Step 1 of 2
Enter =SUBSTITUTE(A2,"red","blue") in B2.
Replacing red with blue in red red gives blue blue.
Formulas here use English function names, such as SUM, and a period as the decimal separator, in every language.
Loading the sheet…
B2 uses SUBSTITUTE.
B2 now shows blue blue.
The next lesson is Picking a result from several tests.
Related functions
SUBSTITUTE is the function that swaps every matching old word for the new word.
Common mistake
Swapping the word order, as in =SUBSTITUTE(B2,"new","old"): old is not found as new, so the label stays old code.