# How to replace multiple values at once with a formula in Excel

Nest one SUBSTITUTE per pair, reading the pairs from a small table: =SUBSTITUTE(SUBSTITUTE(A2,$D$2,$E$2),$D$3,$E$3)…

**Excel:** `=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,$D$2,$E$2),$D$3,$E$3),$D$4,$E$4),$D$5,$E$5)` (Every current version)

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

## Example

Sample data (row 1 is the header):

| A | B | C | D | E |
| --- | --- | --- | --- | --- |
| Address | Standardized |  | Find | Replace |
| 1420 Lake Street |  |  | Street | St |
| 88 Oak Avenue |  |  | Avenue | Ave |
| 7 Elm Court |  |  | Court | Ct |
| 305 River Road |  |  | Road | Rd |
| 12 Pine Lane |  |  |  |  |

Formula in B2, filled to B6: `=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,$D$2,$E$2),$D$3,$E$3),$D$4,$E$4),$D$5,$E$5)`

Result: 1420 Lake St; 88 Oak Ave; 7 Elm Ct; 305 River Rd; 12 Pine Lane

## How it works

Each SUBSTITUTE handles one pair, and its result is the text the next one works on, so all four replacements happen in one formula. Reading the pairs from the table, with locked references, means you can change an abbreviation without editing the formula. "Lane" isn't in the table, so the last row is unchanged.

## Other ways

- `=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"Street","St"),"Avenue","Ave"),"Court","Ct"),"Road","Rd")`: The pairs typed into the formula. Fine for a one-off, harder to maintain.

## Common mistakes

- Replacements hit parts of words: "Road" inside "Broadway" becomes "BRdway". Match whole words by padding with spaces: `SUBSTITUTE(" "&A2&" "," Road "," Rd ")`, then TRIM.
- Order matters when one search word contains another. Put longer words first.
- Excel allows 64 levels of nesting; for long lists use the REDUCE version in the FAQ.

## Questions

**Is there a way without nesting in Excel 365?**

REDUCE loops over the table: `=REDUCE(A2,$D$2:$D$5,LAMBDA(t,f,SUBSTITUTE(t,f,XLOOKUP(f,$D$2:$D$5,$E$2:$E$5))))`.

**Does this work in Google Sheets?**

Yes. Nested SUBSTITUTE works the same, and Google Sheets also has REDUCE and LAMBDA.

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/replace-multiple-values-at-once