Excel Find and Substitute Formula: Examples and Tips

Formulas & functions · Updated November 19, 2026

To find text and replace it in an Excel formula, use SUBSTITUTE: it replaces matching text in a cell, either every occurrence or a specified occurrence. For example, with the text in A2, use =SUBSTITUTE(A2,"old","new"); to find a match’s position without changing the text, use FIND.

Which formula should you use?

Excel has several functions that sound like “find and substitute,” but they do different jobs. Choose based on whether you want to locate text, replace text, or edit a string at a known position:

  • SUBSTITUTE replaces specified text with other text. It is usually the best choice when you know the text to look for and want Excel to return a changed result in another cell.
  • FIND returns the character position where one text string begins inside another. It does not change the cell, and it is case-sensitive.
  • SEARCH also returns a position, but it is not case-sensitive and accepts wildcard characters.
  • REPLACE replaces characters based on their position and length. It does not search for a particular word by itself.

These are worksheet formulas: they produce a result in a cell and leave the original value alone. If you want to change the stored contents of cells directly, use Excel’s Find and Replace command instead. That distinction matters when deciding whether a formula or an editing tool fits the task.

How SUBSTITUTE works

SUBSTITUTE takes the original text, the text to find, and the replacement text. Its optional fourth argument tells Excel which occurrence to replace. If you omit that argument, Excel replaces every exact match. The function is case-sensitive, so lowercase “blue” and uppercase “Blue” are different text.

With the text “blue car, blue bike” in A2, this formula replaces every lowercase occurrence of “blue” with “red” and returns “red car, red bike”:

With the text in A2, the old text in B2, and the replacement in C2, use =SUBSTITUTE(A2,B2,C2). For example, if B2 contains “blue” and C2 contains “red,” the result for “blue car, blue bike” is “red car, red bike.”

To replace only a particular occurrence, include the occurrence number as the fourth argument. With “red, blue, blue” in A2, =SUBSTITUTE(A2,"blue","green",1) returns “red, green, blue.” The number 1 means the first matching occurrence; it does not mean the first character or word in the cell. Use 2 to replace the second match, and so on. If the requested occurrence does not exist, SUBSTITUTE leaves the text unchanged.

SUBSTITUTE searches for a literal sequence of characters, not a whole word. For example, asking it to replace “cat” in “catalog” also changes those three characters. It does not have a “match whole word” option.

Finding a position with FIND or SEARCH

Use FIND when you need the location of a piece of text, perhaps to extract or edit part of a longer value. Its result is a character number, with the first character counted as position 1. It is case-sensitive and does not support wildcards. SEARCH is often a better fit when capitalization may vary; it is case-insensitive and can use the wildcard characters ? and *.

With “Blue car, bike” in A2, =FIND("bike",A2) returns 11 because the “b” in “bike” is the eleventh character. A misspelling, a different capitalization, or a missing search string causes FIND to return the #VALUE! error. With the same text in A2, =SEARCH("BIKE",A2) also returns 11 because SEARCH ignores case.

FIND and SEARCH can accept an optional starting position. That is useful when a string contains the same text more than once: start searching after an earlier match to locate a later one. Remember that the returned position is measured from the start of the complete text, not from the starting point you supplied.

Combine FIND and REPLACE to change a matched string

REPLACE edits a string by position and character count. Pairing it with FIND lets you locate a known string first, then replace the characters at that position. With “Blue car, bike” in A2, this formula replaces the four characters beginning where “bike” is found and returns “Blue car, scooter”:

With the source text in A2, use =REPLACE(A2,FIND("bike",A2),4,"scooter"). FIND supplies the starting position, and 4 is the number of characters to remove before inserting the replacement. This works, but SUBSTITUTE is simpler when the goal is just to replace known text.

If the search term can be absent, FIND will return an error and that error will pass through REPLACE. You can handle that by returning the original text when no match is found. With the source in A2, search term in B2, and replacement in C2, use =IFERROR(REPLACE(A2,FIND(B2,A2),LEN(B2),C2),A2). For “Blue car, bike” in A2, “train” in B2, and “scooter” in C2, the result remains “Blue car, bike.” If B2 contains “bike,” the result is “Blue car, scooter.” Avoid leaving B2 blank: an empty search string can produce an unexpected insertion rather than a meaningful replacement.

Use cell references for repeatable replacements

Typing the old and new text directly into a formula is convenient for a one-off change. Cell references are easier to maintain when you may change the replacement later or apply the same rule to many rows. For example, keep source text in column A, the search text in B1, and the replacement in C1. With those cells, enter =SUBSTITUTE(A2,$B$1,$C$1) in D2. The dollar signs keep the search and replacement references fixed when you fill the formula down; A2 changes to A3, A4, and so on.

To apply the formula to more rows, select D2 and drag the fill handle down, or copy and paste it into the other result cells. Check that the references still point to the intended source cells and shared search and replacement cells. If a replacement rule contains a blank search cell, correct that input before filling the formula, since an empty search value is not a useful target.

Formulas return text, even when the result looks like a number or date. If you substitute characters in an identifier such as a product code, that is usually appropriate because codes are text-like. If you plan to calculate with the result, check its data type and formatting rather than assuming a text result will behave like a number.

Formula versus Excel Find and Replace

Use a formula when you want to preserve the original data, show a transformed result beside it, or make the output update when source text changes. Use Find and Replace when you intend to edit existing cells in place. On Excel for Microsoft 365 for Windows, press Ctrl+H to open the Replace tab of Find and Replace; the ribbon route is Home > Find & Select > Replace. On a Mac, use Edit > Find > Replace; available keyboard shortcuts can differ with the Excel version and keyboard settings.

The dialog is more suitable for a one-time cleanup across a selected range, sheet, or workbook. Review its options before choosing Replace All: scope, case matching, and whether Excel searches formulas or displayed values can affect what is changed. Select a limited range first if you do not want the entire sheet or workbook affected. A formula is safer for review because the source remains visible, but its output is not a permanent edit until you copy the results and paste them as values.

Neither method automatically understands context. Replacing “in” in a list of words may alter the middle of longer words, and a broad Replace All operation can change more cells than intended. If whole-word matching is essential, inspect the results or use a method designed for the data’s structure; SUBSTITUTE has no whole-word switch.

Common errors and troubleshooting

A replacement formula can return an unexpected result even when its syntax is valid. Check the following before assuming Excel is malfunctioning:

  • Only some capitalization changes: SUBSTITUTE is case-sensitive. Check whether the source contains mixed capitalization. SEARCH is case-insensitive for locating text, but it does not perform the replacement.
  • The formula returns #VALUE!: FIND could not locate the requested text, or its capitalization did not match. Confirm the spelling and consider SEARCH if case should not matter. IFERROR can provide a deliberate fallback.
  • More text changes than intended: SUBSTITUTE replaces every match unless you supply an occurrence number. It also replaces matching character sequences inside longer words.
  • The formula appears as text instead of calculating: Check whether the cell is formatted as Text, or whether an apostrophe appears before the equals sign. Change the cell format to General, then re-enter the formula.
  • The result seems unchanged: The old text may not occur exactly as typed, or the specified occurrence number may be greater than the number of matches. SUBSTITUTE returns the original text when that occurrence is missing.

When checking an unexpected output, compare the source and result character by character. Extra spaces, punctuation, and nonprinting characters can make text look identical while preventing an exact match. If the data came from another system, first inspect a few representative values and decide whether cleanup is needed before replacing text broadly.

Frequently Asked Questions

What is the Excel formula to find and replace text?
For a straightforward replacement, use SUBSTITUTE. With the text in A2, =SUBSTITUTE(A2,"old","new") replaces every exact occurrence of “old” with “new” and returns the changed text in the formula cell.

How do I replace only the first occurrence?
With the source text in A2, use =SUBSTITUTE(A2,"old","new",1). The final argument specifies the first occurrence. Use 2 to replace the second occurrence instead.

Is Excel SUBSTITUTE case-sensitive?
Yes. SUBSTITUTE distinguishes uppercase and lowercase text. For example, searching for “blue” does not replace “Blue.” SEARCH ignores case when locating text, but to perform a case-insensitive replacement you need a different approach than simply swapping SUBSTITUTE for SEARCH.

What is the difference between FIND and SUBSTITUTE?
FIND returns the position of a matching text string and does not change it. SUBSTITUTE returns text with a matching string replaced. Use FIND when you need a location; use SUBSTITUTE when you want changed text.

Can I replace text directly in the original cells?
Yes. Use Find and Replace (Ctrl+H on Windows) and review the search scope and options before using Replace All. A worksheet formula instead creates a separate result; it does not overwrite its source cell.

Can SUBSTITUTE replace whole words only?
No. It replaces a matching sequence wherever it appears, including inside longer words. Check the results carefully when the search text is short or could occur within other terms.

Related

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.