Text & String Manipulation
Find and Replace Multiple Values in One Formula (Readable LET + SUBSTITUTE Pipeline)
Standardize multiple street, unit, or product abbreviations in a single formula without unreadable nested SUBSTITUTE pyramids by naming each replacement step inside LET.
Replacing unreadable nested SUBSTITUTE pyramids with sequential LET steps
Nesting four or five SUBSTITUTE calls—SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2, ...))))—separates each search token from its replacement string across opposite ends of the formula, making maintenance error-prone.
Naming intermediate stages step_1, step_2, step_3, and step_4 inside LET pairs every Find cell ($D$2) right next to its Replace cell ($E$2) on its own logical step and keeps replacement order deterministic (longest tokens first so "Dept." is replaced before "Dep").
| Rule Step | Find Token | Replace Token | Why Order Matters |
|---|---|---|---|
| step_1 | "St." | "Street" | Replaces dotted abbreviation cleanly |
| step_2 | "Ave." | "Avenue" | Expands avenue designator |
| step_3 | "Ste." | "Suite" | Standardizes unit identifier |
| step_4 | " " -> TRIM | Single spaces | Cleans double spaces left by blank removals |
Table-Driven Multi-Replace LET Formula
Reference a 4-row Find/Replace mapping table ($D$2:$E$5) step by step inside LET, guarding blank replacement cells with IF(ISBLANK($E$5), "", $E$5) so intentional token removals never coerce to "0".
=LET(step_1, SUBSTITUTE(A2, $D$2, IF(ISBLANK($E$2), "", $E$2)), step_2, SUBSTITUTE(step_1, $D$3, IF(ISBLANK($E$3), "", $E$3)), step_3, SUBSTITUTE(step_2, $D$4, IF(ISBLANK($E$4), "", $E$4)), step_4, SUBSTITUTE(step_3, $D$5, IF(ISBLANK($E$5), "", $E$5)), TRIM(step_4))Try the example
Use the original values or change them to test your understanding.