Find and Replace Text in a Formula

All versionsSUBSTITUTEREPLACE

To swap text inside a formula — reformat phone numbers, fix a recurring typo, change a delimiter — use SUBSTITUTE (replace by matching text) or REPLACE (replace by position). Unlike Find & Replace, these update automatically as data changes.

Quick formula: to replace every “-” in A2 with a space:

=SUBSTITUTE(A2, "-", " ")

SUBSTITUTE swaps all occurrences of the old text for the new — here, dashes become spaces.

Functions used (tap for the full reference guide):

SUBSTITUTE REPLACE

The example

Reformat dashed codes into spaced ones.

	A	B
1	Raw	Replaced
2	A-100-X	A 100 X
3	B-205-Y	B 205 Y

The formula

Dashes replaced with spaces:

=SUBSTITUTE(A2, "-", " ") // "A-100-X" → "A 100 X"

How it works

SUBSTITUTE matches text; REPLACE matches position:

SUBSTITUTE(text, old, new) finds every old string and swaps in new — all dashes become spaces.
Add a 4th argument to target a specific occurrence: SUBSTITUTE(A2, "-", " ", 2) changes only the 2nd dash.
REPLACE(text, start, length, new) works by position instead — replace 3 characters starting at position 1, regardless of what they are.
Both rebuild the string live, so editing the source updates the result.

SUBSTITUTE vs Find & Replace. The Ctrl+H dialog is a one-time edit of the cells themselves; SUBSTITUTE is a live formula that keeps reformatting as the source changes — better for ongoing imports.

Try it: interactive demo

Live demo

Set the text, what to find, and what to replace it with.

Text (A2)

Find

Replace

Result:

Variations

Replace only the Nth occurrence

The 4th argument targets one instance:

=SUBSTITUTE(A2, "-", " ", 2)

Remove text entirely

Replace with an empty string:

=SUBSTITUTE(A2, "-", "")

REPLACE by position

Swap the first 3 characters for a new prefix:

=REPLACE(A2, 1, 3, "NEW")

Pitfalls & errors

SUBSTITUTE is case-sensitive. “cat” won’t match “Cat.” Match the exact case, or normalize with LOWER first.

SUBSTITUTE matches text; REPLACE matches position. Use SUBSTITUTE when you know what to change, REPLACE when you know where.

Nested SUBSTITUTEs get unreadable. For many swaps, stack them carefully or use a lookup table approach instead.

Practice workbook

📊

Download the free Find and Replace Text in a Formula practice workbook

Coded strings with live SUBSTITUTE, the Nth-occurrence, remove, and REPLACE-by-position variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I replace text within a formula in Excel?

Use =SUBSTITUTE(text, old, new), e.g. =SUBSTITUTE(A2, "-", " ") swaps every dash for a space. It updates live, unlike the Find & Replace dialog.

What's the difference between SUBSTITUTE and REPLACE?

SUBSTITUTE replaces text by matching a string; REPLACE replaces by character position (start and length). Use SUBSTITUTE when you know what to change, REPLACE when you know where.

How do I replace only one occurrence?

Add the instance number as SUBSTITUTE's fourth argument: =SUBSTITUTE(A2, "-", " ", 2) changes only the second dash.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Function references: SUBSTITUTE · REPLACE