# How to find and replace text with a formula in Excel

Use =SUBSTITUTE(A2,"old","new"). It replaces every occurrence of the old text, and the original cell stays unchanged.

**Excel:** `=SUBSTITUTE(A2,$E$1,$G$1)` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F | G |
| --- | --- | --- | --- | --- | --- | --- |
| Product | Renamed |  | Find | Gray | Replace with | Graphite |
| Desk lamp - Gray |  |  |  |  |  |  |
| Office chair - Gray mesh |  |  |  |  |  |  |
| Monitor arm - Silver |  |  |  |  |  |  |
| Standing desk - Gray/Oak |  |  |  |  |  |  |
| Cable tray - gray |  |  |  |  |  |  |

Formula in B2, filled to B6: `=SUBSTITUTE(A2,$E$1,$G$1)`

Result: Desk lamp - Graphite; Office chair - Graphite mesh; Monitor arm - Silver; Standing desk - Graphite/Oak; Cable tray - gray

## How it works

SUBSTITUTE swaps every copy of the text in E1 for the text in G1. Unlike Ctrl+H, it leaves the original column alone and updates when the inputs change. It is case-sensitive, which is why "gray" in the last row isn't replaced.

## Other ways

- `=IFERROR(REPLACE(A2,SEARCH($E$1,A2),LEN($E$1),$G$1),A2)`: Case-insensitive: SEARCH finds the text whatever its case and REPLACE swaps it. It replaces only the first match. (The last row also becomes "Cable tray - Graphite".)

## Common mistakes

- SUBSTITUTE matches inside longer words too: replacing "Gray" also changes "Grayson".
- REPLACE is a different function: it swaps characters at a position, not a piece of text. Use SUBSTITUTE when you know the text.

## Questions

**How do I replace several different words?**

Nest SUBSTITUTE: `=SUBSTITUTE(SUBSTITUTE(A2,"Gray","Graphite"),"Oak","Walnut")`.

**How do I replace only the first occurrence?**

Add the instance number: `=SUBSTITUTE(A2,$E$1,$G$1,1)`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/find-and-replace-text-with-a-formula