Wauvel

Excel like a finance pro.

The functions, VBA, and shortcuts a finance operator actually uses — curated, with CFO examples. Prefer them pre-built? Grab a ready-made model.

← All functions

SUBSTITUTE

Text & cleanup

Find-and-replace inside a formula — standardize abbreviations and strip unwanted characters.

Difficulty

Amateur
Excel file

1What is it?

SUBSTITUTE replaces every occurrence of a specific piece of text with another — a live, in-formula find-and-replace. It's how you standardize messy data without touching the source: turn "St" into "Street", strip the "$" and "," out of a value pasted as text, or remove a stray character. Give it an instance number to replace only the nth occurrence. (SUBSTITUTE matches text; REPLACE swaps by position instead.)

2What it looks like

SUBSTITUTE(text, old_text, new_text, [instance_num])
text
The string to work on.
old_text
The exact text to find (case-sensitive).
new_text
What to put in its place — use "" to delete the old text entirely.
[instance_num]
Replace only the nth occurrence; omit to replace them all.

3When you use it

  • Standardize addresses — "St" → "Street", "Ave" → "Avenue".
  • Strip "$" and "," from a value pasted as text so it can become a number.
  • Remove or convert a non-breaking space: SUBSTITUTE(text, CHAR(160), " ").

4See it in action

Change the inputs — the formula and result update live. Prefer the real thing? Download the Excel file and open it in Excel.

Start simple: standardize an abbreviation · 1 of 2

Replace every "St" with "Street". Edit the address below.

C2
fx
=SUBSTITUTE(B2, "St", "Street")123 Main Street
ABC
1FieldValueResult
2Address123 Main St123 Main Street

The lime cell holds the formula — click it (or any cell) to see its contents in the bar above, just like Excel. Edit the blue cells to watch it recompute.

5Common errors

Replaces too muchold_text appears inside other words (e.g. "St" inside "Street").

Fix: Include surrounding spaces (" St"), or target one occurrence with instance_num.

Nothing changesold_text doesn't match exactly — SUBSTITUTE is case-sensitive.

Fix: Match the case precisely, or normalize with UPPER/LOWER first.

Still text, not a numberYou stripped the symbols but the cell is still text.

Fix: Wrap in VALUE(), or multiply by 1, to turn the cleaned string into a real number.

6Better functions & alternatives

  • REPLACE — Swaps characters by position (start, length) rather than by matching text.
  • TRIM — For the specific job of removing extra spaces.
  • Find & Replace — Ctrl+H for a one-off, in-place change when you don't need a formula.

Want SUBSTITUTE already wired into a model? Wauvel's free tools download as branded, formula-driven Excel.

Learn the moves here — or let Wauvel run them on your numbers.

Get my free report →