Find and Replace Formula in Excel: SUBSTITUTE or REPLACE

In Excel, use SUBSTITUTE to replace matching text inside a cell, or REPLACE to replace characters at a specified position. For a one-time edit to existing cells, use Home > Find & Select > Replace instead; that command changes the selected cell contents rather than returning a separate formula result.

What Find and Replace Means in Excel

The phrase find and replace formula can refer to two different jobs. You might want a formula that produces revised text from an original value, or you might want to edit existing worksheet data with Excel’s Find and Replace dialog. These approaches are not interchangeable: a formula keeps the original data and calculates a result elsewhere, while the dialog edits the cells you choose.

For example, if a product code contains an outdated year, a formula can show the corrected code in a new column while leaving the source intact. That is useful when you need to review changes, retain the original records, or have the result update automatically when source data changes. If you simply need to correct a known typo in a small set of cells, Find and Replace may be quicker.

Excel does not have a general worksheet function that searches and edits arbitrary cells in place. Worksheet formulas return values; they do not overwrite the cells they refer to. Choose the method based on whether you need a calculated output or a permanent edit.

SUBSTITUTE Versus REPLACE

SUBSTITUTE searches for a particular text string and swaps it for another. It is the usual choice when the text to change is identifiable by its contents, wherever it appears in the cell. It replaces every matching occurrence by default, or just the occurrence you specify with its optional fourth argument.

With the original text in A2, this formula changes every occurrence of the word blue to green:

Assumed layout: A2 contains Blue, blue, BLUE. Formula: =SUBSTITUTE(A2,"blue","green"). Result: Blue, green, BLUE.

The result demonstrates an important detail: SUBSTITUTE is case-sensitive. It changes the lowercase instance, but not the initial-capital or uppercase instances. It also returns text; it does not alter A2.

REPLACE, by contrast, works by character position. It takes the original text, the starting character number, the number of characters to remove, and the replacement text. Use it when the part to change always occupies a fixed position, such as a year in a consistently formatted code.

Assumed layout: A2 contains INV-2026-047, with the four-digit year starting at character 5. Formula: =REPLACE(A2,5,4,"2027"). Result: INV-2027-047.

If the code format varies, positional replacement can change the wrong characters. In that case, use SUBSTITUTE to target a specific old string, or first standardize the source data.

Examples for Common Text Changes

These examples use ordinary cell references and can be filled down a column. Each formula returns a changed value in the cell where you enter it, leaving the referenced source cell untouched.

Change a label: Assumed layout: A2 contains West Region. Formula: =SUBSTITUTE(A2,"West","Central"). Result: Central Region.
Remove a character: Assumed layout: A2 contains AB-104-XY. Formula: =SUBSTITUTE(A2,"-",""). Result: AB104XY.
Change just the second occurrence: Assumed layout: A2 contains Q1-Q1-Q1. Formula: =SUBSTITUTE(A2,"Q1","Q2",2). Result: Q1-Q2-Q1.
Change characters at a fixed position: Assumed layout: A2 contains ABC-00058, and the five digits begin at character 5. Formula: =REPLACE(A2,5,5,"91234"). Result: ABC-91234.

For a simple replacement, the old text can be longer than one character, and the replacement can be empty to remove it. Check the source and result side by side before using the output in reports or exports, particularly when codes have leading zeros or depend on a precise format.

How to Use Find and Replace in the Ribbon

To edit cell contents directly in Excel for Microsoft 365 on Windows, select the relevant range if you want to limit the operation, then choose Home > Find & Select > Replace. You can also press Ctrl+H. Enter the text to search for in Find what and the new text in Replace with.

Before choosing Replace All, consider these checks:

Select only the intended cells if the replacement should apply to part of a worksheet.
Review the options in the Find and Replace dialog, including whether Excel searches the current sheet or the workbook and what it looks in.
Use Find Next or Replace to inspect a match first when the old text might occur in unrelated values.
Choose Replace All only when every matching instance in the chosen search scope should change.
Read Excel’s confirmation message, then inspect representative results and undo with Ctrl+Z promptly if the change was unintended.

In Excel for Mac, the Replace command is available from Edit > Find > Replace; keyboard shortcuts can differ by keyboard and version, so use the menu if Ctrl+H does not open the expected dialog. Excel for the web has a Find and Replace option as well, though its interface and available options can differ from the Windows desktop app.

Case, Whole-Cell Matches, and Search Scope

A formula and the Find and Replace dialog may treat matching differently. SUBSTITUTE is case-sensitive, so changing north does not automatically change North. In the dialog, the Match case option controls whether letter case matters. The Match entire cell contents option is useful when you want to replace a whole cell that exactly matches a search term, rather than a portion of a longer entry.

Search scope matters just as much. A search can be limited to a selection or applied across a sheet or workbook, depending on the dialog’s settings. A replacement intended for a status column could otherwise alter matching words in notes, headers, or formulas elsewhere. If you are unsure, search first and inspect the matches before replacing them.

Another common misconception is that a formula replacement understands words or meanings. SUBSTITUTE replaces a matching sequence of characters, not a linguistic word. Replacing cat can also affect a longer string containing those letters. Use carefully chosen search text and inspect the output when partial matches could damage names, identifiers, or other values.

Replacing Text Inside Formulas

Find and Replace can search formulas as well as displayed values. In the dialog, check the Look in setting and choose Formulas if the goal is to edit formula text. This is different from replacing text in a cell’s displayed result: the formula itself is changed, which can alter what the cell calculates.

For example, replacing one worksheet reference with another across many formulas can be useful during a planned model update, but a broad replacement may also change unrelated references or names. Search a narrow range, inspect matches, and save a copy before making large-scale formula edits. A change to formulas can have effects beyond the cells initially selected if other calculations depend on them.

Do not expect SUBSTITUTE to turn a text string that looks like a formula into a working formula. A regular worksheet formula can produce text, but Excel does not generally evaluate arbitrary text as a new formula through a standard worksheet function. If the task is to revise formulas, use the Find and Replace dialog carefully or edit the formulas directly.

Common Problems and How to Avoid Them

The formula returns the original text: Check that the old text exactly matches the source, including spaces and punctuation. Remember that SUBSTITUTE distinguishes uppercase and lowercase letters.
Only some instances change: If you supplied the optional instance number to SUBSTITUTE, only that occurrence is replaced. Remove that argument to replace all matching instances.
The wrong characters change: This often happens with REPLACE when the starting position or character count is wrong. Count the characters in a representative source value and confirm that the format is consistent.
The result loses a meaningful format: Identifiers such as postal codes and product numbers may contain leading zeros. Check whether the source is stored as text or a number, and verify the displayed result before relying on it.
Replace All changes too much: Recheck the selected range, search scope, and matching options. Undo immediately if needed, then narrow the target and test a single replacement.

When the same change must be applied repeatedly to incoming data, a formula in a helper column is often safer than editing the source by hand. Once you have checked the results, you can copy the output and use Paste Special > Values if you need fixed text rather than formulas. Keep an untouched source copy if the original values may be needed later.

Frequently Asked Questions

What formula replaces text in Excel?
Use SUBSTITUTE when you know the text to find, or REPLACE when you know the character position and length. Both return a result in the formula cell and leave the source cell unchanged.

How do I replace only one occurrence?
With the text in A2, use =SUBSTITUTE(A2,"old","new",2) to replace the second occurrence of old. The fourth argument is the occurrence number; leave it out to replace every matching occurrence.

Is SUBSTITUTE case-sensitive?
Yes. It matches the exact letter case in the old-text argument. Excel’s Find and Replace dialog has a Match case option for controlling case during a direct replacement.

Can I replace text in place with a formula?
No. A worksheet formula returns a result in its own cell; it does not overwrite its source. Use Find and Replace for an in-place edit, or calculate results in a helper column and paste the verified results as values if appropriate.

How do I replace text in Excel formulas?
Open Home > Find & Select > Replace, set Look in to Formulas, and search within a limited range where possible. Review matches carefully because replacing formula text can change calculations and references.

Should I use SUBSTITUTE or Find and Replace?
Use SUBSTITUTE for a repeatable, reviewable result that updates with its source. Use Find and Replace for a one-time edit to existing cells, after confirming the search scope and what Excel is searching.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.