Excel Formula to Find and Replace Text in Cells: Examples

To replace text inside a cell with an Excel formula, use SUBSTITUTE. If the original text is in A2, this formula changes every instance of AB- to ZX-: =SUBSTITUTE(A2,"AB-","ZX-"). It returns a changed result in the formula cell and leaves A2 unchanged.

Which Excel function should you use?

SUBSTITUTE is usually the best choice when you know the text to find and want to replace it wherever it occurs. For example, it can change a product code prefix, remove a punctuation mark, or standardize a word in a list. Its arguments are the text to examine, the exact text to find, the replacement text, and—optionally—which occurrence to replace.

Use REPLACE instead when the characters to change are identified by their position and length, rather than by their content. Use FIND or SEARCH when you need to locate a piece of text first, perhaps because its position varies from one cell to another. These functions can be combined with REPLACE for a targeted change.

Known text, all matches: SUBSTITUTE
Known text, one selected match: SUBSTITUTE with its optional occurrence argument
Known character position and length: REPLACE
Variable position, case-sensitive search: FIND with REPLACE
Variable position, case-insensitive search: SEARCH with REPLACE

Replace every matching piece of text with SUBSTITUTE

SUBSTITUTE searches for a specified sequence of characters and replaces matching instances. It does not require the text to be at a fixed position or to occupy the entire cell. That makes it useful for editing a phrase or code embedded in longer text.

With the text Order: AB-104 in A2, the formula =SUBSTITUTE(A2,"AB-","ZX-") returns Order: ZX-104. The formula’s first argument is the source cell; its second and third arguments are the old and new text. If the old text appears more than once, Excel replaces every occurrence unless you specify an occurrence number.

For example, with A2 containing red red, =SUBSTITUTE(A2,"red","green") returns green green. If you want to change only the second instance, add 2 as the fourth argument: with that same cell layout, =SUBSTITUTE(A2,"red","green",2) returns red green. The occurrence number counts matching instances from left to right.

The match is case-sensitive. With A2 containing North NORTH, =SUBSTITUTE(A2,"North","South") returns South NORTH: the second spelling does not match the capitalization in the formula. If capitalization varies and should not matter, see the SEARCH method below.

Change text based on its position with REPLACE

REPLACE changes a specified number of characters starting at a specified position. Its arguments are the source text, the starting character number, the number of characters to replace, and the replacement text. Unlike SUBSTITUTE, it does not search for a particular old word or code.

With A2 containing INV-2026-0041, =REPLACE(A2,5,4,"2025") returns INV-2025-0041. Character 5 is the first digit of 2026, and four characters are replaced. This approach is appropriate if the year is always in that position and has the same length.

Position-based replacement can produce unwanted results if the input strings are not consistent. If a prefix is sometimes longer, or the part to change can move, use SUBSTITUTE to identify it by its text. If the position varies but the text can be found, combine a search function with REPLACE.

Find a variable position with FIND or SEARCH

FIND returns the position of one text string inside another and is case-sensitive. SEARCH also returns a position, but it ignores differences in capitalization. Both return a number, not the changed text, so combine the result with REPLACE to make a replacement.

With A2 containing Region: NORTH, =REPLACE(A2,SEARCH("north",A2),LEN("north"),"South") returns Region: South. SEARCH finds the start of NORTH without regard to case, and LEN supplies the number of characters to replace. Because the location is found from the cell’s content, the formula can still work if the region name appears at a different position in another cell.

To make the search case-sensitive, use FIND in the same pattern. With A2 containing Region: north, =REPLACE(A2,FIND("north",A2),LEN("north"),"South") returns Region: South. If the capitalization does not match the search text, FIND returns an error instead of a position.

When a searched-for string might be absent, you can use IFERROR to keep the original text instead of displaying an error. With A2 containing Region: East, B2 containing north, and C2 containing South, =IFERROR(REPLACE(A2,SEARCH(B2,A2),LEN(B2),C2),A2) returns Region: East because the search term is not found. This pattern is useful for mixed data, but it also means a failed search can pass through unnoticed, so check whether that behavior suits your task.

Apply more than one replacement

You can nest SUBSTITUTE functions when a cell needs several different changes. Each inner function produces text that the next function can process. For example, with A2 containing AB- 104, =SUBSTITUTE(SUBSTITUTE(A2,"-","")," ","") returns AB104: the inner function removes the hyphen, and the outer one removes the space.

Order matters when replacement rules overlap. If one replacement creates text that matches a later search term, the later function may change that newly created text too. For a long list of changing rules, a formula with many nested functions can become difficult to read and maintain. Consider using separate helper columns, or a Power Query transformation when you need a repeatable cleanup process across a large imported dataset.

To remove a known word, set the replacement text to an empty string. With A2 containing Draft – North, =SUBSTITUTE(A2,"Draft - ","") returns North. An empty replacement removes only the specified characters; it does not automatically remove extra spaces elsewhere in the cell.

Put the formula in a helper column and keep the source

A formula cannot change its own source cell while also referring to that cell as its input. For a list in column A, enter the formula in B2, then fill it down to process the remaining rows. In Microsoft 365 for Windows, select B2 and drag its fill handle down, or double-click the fill handle to copy the formula alongside adjacent data. Excel adjusts relative references as the formula fills—for example, a reference to A2 becomes A3 in the next row.

Keeping the original column is often helpful: you can compare results, spot unexpected substitutions, and return to the source data if needed. If you want to replace the original values with the formula results, first review the output, then copy the result cells and use Home > Paste > Values over the original range. Pasting values replaces the formulas with their current results. Keep a copy of the original data before overwriting it, especially if the workbook contains important records.

Formulas recalculate when their inputs change. If A2, B2, or C2 changes, a formula referring to those cells can produce a different result. A direct replacement formula such as =SUBSTITUTE(A2,"AB-","ZX-") does not permanently edit A2; it displays the result wherever the formula is entered.

Formula replacement versus Find and Replace

A formula is useful when you need to preserve the source, show the transformed result in another column, or apply the same rule to a changing list. Excel’s Find and Replace command is better for a one-time edit when you want to alter the selected cells directly. On Windows, open Home > Find & Select > Replace. In Excel for Mac, use Edit > Find > Replace. Review the search options and selected range before choosing Replace All, because that command can affect more cells than intended.

Find and Replace is an editing command, not a formula. It changes matching content in the cells it processes, while a formula returns a result in a separate cell. For a large or valuable range, test the search on a small selection first. If you want a formula’s results to become fixed text, use Paste Values after checking them; if you want to retain an editable rule, keep the formulas and their source cells.

Common issues and checks

No change appears: Check that the old text matches the cell content. SUBSTITUTE is case-sensitive, and leading or trailing spaces can make a match different from what it looks like.
The result contains an error: A FIND or SEARCH formula returns an error if its search text is absent. Use IFERROR only if returning the original value is the right fallback.
Only some rows change: Look for differences in punctuation, spacing, capitalization, or spelling. A formula replaces the string you specify, not every similar-looking variation.
The formula appears as text: Check whether the cell is formatted as Text or whether an apostrophe precedes the equals sign. Change the cell format to General, then re-enter the formula.
Leading zeros disappear: If a code such as 0041 must remain exactly as written, store and treat it as text. A number format can display leading zeros without making them part of the underlying text value.

Excel formulas use commas between arguments in the examples here. Some regional settings use semicolons as list separators, so Excel may require semicolons when you enter a formula. If Excel rejects a formula copied from an example, check the list separator setting used by your installation.

Frequently Asked Questions

Can an Excel formula replace text in the same cell?
No. A formula that refers to its own cell would create a circular reference. Put the formula in another cell, then copy and paste its values over the source if you want to replace the original content.

How do I replace only the first occurrence?
Use SUBSTITUTE with its optional fourth argument set to 1. With A2 containing cat and cat, =SUBSTITUTE(A2,"cat","dog",1) returns dog and cat. Change the argument to 2 to replace the second match instead.

Is SUBSTITUTE case-sensitive in Excel?
Yes. SUBSTITUTE matches the capitalization of the text you provide. For a case-insensitive search in a formula, use SEARCH to find the position and REPLACE to change the matching characters.

Does SUBSTITUTE replace text inside a longer word?
Yes. It replaces matching character sequences wherever they occur, including within longer text. If that could change unintended words, make the search term more specific or use a method that checks the surrounding text.

Can I replace several different strings at once?
You can nest multiple SUBSTITUTE functions, with each one handling a different string. For many rules or repeated imports, helper columns or Power Query may be easier to review and update than one long nested formula.

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.