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

REPLACE

Text & cleanup

SUBSTITUTE replaces what text says. REPLACE replaces where it sits.

Difficulty

Amateur

Excel file

1What is it?

SUBSTITUTE finds a piece of text wherever it appears and swaps it. REPLACE ignores content entirely and works by position: starting at character 5, replace the next 3 characters. That's the right tool for anything fixed-width — an account code where characters 5 to 7 are always the department, a reference number with a prefix that needs changing, a masked card number. It's also safer than SUBSTITUTE when the text you want to change also appears elsewhere in the string, because it only touches the position you name.

2What it looks like

REPLACE(old_text, start_num, num_chars, new_text)

old_text: The original string.
start_num: Position of the first character to replace, counting from 1.
num_chars: How many characters to remove. Use 0 to INSERT new_text without removing anything.
new_text: What goes in their place. Can be longer or shorter than what came out.

3When you use it

Change the department segment in a fixed-width account code.
Mask all but the last four digits of an account number.
Insert a separator at a fixed position, with num_chars 0.

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.

Characters 6-8 of this account code are the department.

=REPLACE(A2, 6, 3, "310")SUBSTITUTE(A2, "200", "310") → 6100-310-01

	A
1	Result
2	SUBSTITUTE(A2, "200", "310") → 6100-310-01
3	Same answer here. Try 6200-200-01 to see SUBSTITUTE change both.

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.

Account code

New department

5Common errors

#VALUE!start_num or num_chars is negative, or start_num is 0.

Fix: Positions count from 1. Build them from FIND if they vary.

Replaced the wrong charactersThe codes aren't actually fixed-width — some have a shorter first segment.

Fix: Check with LEN first. If the widths vary, find the position with FIND("-", A2) instead of assuming it.

Result is text, not a numberREPLACE always returns text, even on digits.

Fix: Wrap in VALUE if the result needs to be numeric.

6Better functions & alternatives

SUBSTITUTE — When you know WHAT to change rather than where it is.
LEFT / MID / RIGHT — Rebuild the string from its pieces when the change is more than one segment.

Want REPLACE 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.

Meet your AI CFO →

One CFO-grade Excel tip a week

A short, practical email for finance operators — functions, shortcuts, and the moves that save an afternoon. Free, unsubscribe anytime.