Excel SUBSTITUTE Formula: Replace Text in Cells Precisely

Formulas & functions · Updated November 19, 2026

Use Excel’s SUBSTITUTE function to replace matching text in a cell: with the original text in A2, the text to find as a hyphen, and a space as the replacement, enter =SUBSTITUTE(A2,"-"," "). By default, it replaces every occurrence of the old text; an optional fourth argument limits the change to a particular occurrence.

What the SUBSTITUTE function does

SUBSTITUTE returns a new text value after replacing one piece of text with another. It does not edit the source cell, so the original value stays intact while the formula result appears in the cell containing the formula. This makes it useful for cleaning imported data, standardizing labels, and preparing text for a report or lookup.

The function searches for an exact text sequence, which can be a whole word, a punctuation mark, a space, or just a few characters inside a longer string. For example, changing a hyphen to a space can turn a code such as North-West into North West. Replacing a repeated word or symbol works the same way.

SUBSTITUTE is available in Excel for Microsoft 365 on Windows, Mac, and the web. The function and its argument order work the same across those versions. Depending on regional settings, Excel may display semicolons rather than commas between arguments; use the separator configured for your installation.

Arguments and basic syntax

The function takes three required arguments, followed by one optional argument. In order, they are the text to change, the old text to find, and the new text to insert. The optional instance number tells Excel which occurrence to replace. If you leave it out, every matching occurrence is changed.

Arguments can be typed as quoted text or supplied by cell references. Quotation marks are required around literal text in a formula, including a single space. A reference such as A2 does not need quotation marks. The result is text, even if the replacement leaves characters that look like a number.

  • Text: The source string or cell to process.
  • Old text: The exact sequence Excel should search for.
  • New text: The sequence to put in its place. Use an empty string, written as two quotation marks with nothing between them, to remove matches.
  • Instance number: Optional; a positive occurrence number, such as 2 for the second match.

Replace every occurrence in a cell

Suppose A2 contains the text North-West. To replace the hyphen with a space, enter this formula in B2. The result is North West. The source in A2 is not changed, and you can fill the formula down to process other rows.

With North-West in A2, this formula returns North West: =SUBSTITUTE(A2,"-"," ")

SUBSTITUTE can also remove a character. If A2 contains the product code AB-12-XY, replacing each hyphen with empty text returns AB12XY. This is different from replacing a hyphen with a space, which would preserve a visible separator between the code parts.

With AB-12-XY in A2, this formula returns AB12XY: =SUBSTITUTE(A2,"-","")

For data in a column, enter the formula next to the first row and copy it downward. In Microsoft 365, you can also use the fill handle to extend the formula. Check a few results before relying on the cleaned column, especially when the input includes inconsistent spacing or punctuation.

Replace only a specific occurrence

The optional fourth argument is useful when a character appears several times but only one should change. For example, a list may use commas as separators, and you may want to alter only the second comma. Specify 2 as the instance number. Excel counts matching occurrences from left to right, starting with 1.

With red,blue,green,blue in A2, this formula changes only the second occurrence of blue to gold and returns red,blue,green,gold: =SUBSTITUTE(A2,"blue","gold",2)

If the requested occurrence does not exist, Excel leaves the text unchanged rather than reporting an error. If the instance number is zero or negative, the function returns a #VALUE! error. The number must identify an occurrence, not a character position; for replacing text at a fixed position, a different function may be more appropriate.

Make several substitutions

A single SUBSTITUTE call searches for one old-text sequence. To change two different characters, nest one call inside another: the inner function runs first, and its result becomes the text processed by the outer function. This can handle small, predictable cleanup tasks without changing the original value.

With a value containing regular spaces and hyphens, such as 555-0100 with spaces around it in A2, this formula removes hyphens first and then removes ordinary spaces, returning 5550100: =SUBSTITUTE(SUBSTITUTE(A2,"-","")," ","")

Order matters when one replacement creates text that a later substitution also targets. For instance, replacing a symbol with a letter and then replacing that letter can affect both the newly inserted letter and any matching letters already present. Plan the sequence and check a test row before filling a nested formula through a large dataset.

When many different replacements are needed, a long nest can become difficult to read and maintain. Consider using a small, documented series of helper columns, or use Excel’s Find and Replace for a one-time edit. A formula is preferable when the transformation should update automatically as source values change.

Case sensitivity, spaces, and exact matches

SUBSTITUTE is case-sensitive. If the old text is lowercase apple, it matches lowercase apple but not uppercase Apple. That precision is helpful when capitalization carries meaning, but it can surprise someone expecting a case-insensitive search.

With Apple apple in A2, this formula changes only the lowercase occurrence and returns Apple pear: =SUBSTITUTE(A2,"apple","pear")

The function does not interpret asterisks or question marks as wildcard characters. It searches for those characters literally. It also does not automatically trim spaces or treat different kinds of spaces as identical. A normal space and a nonbreaking space can look alike on screen while being different characters.

Imported web text sometimes contains a nonbreaking space. If A2 contains that character between words, replacing it with an ordinary space can make the text easier to clean or compare. CHAR(160) represents the nonbreaking space in this formula; the returned space is a normal keyboard space.

With text in A2 that may contain nonbreaking spaces, this formula replaces each one with an ordinary space: =SUBSTITUTE(A2,CHAR(160)," ")

SUBSTITUTE compared with REPLACE and Find and Replace

Choose SUBSTITUTE when the target is identified by its content: replace every slash, change a specific word, or remove a repeated suffix. Choose REPLACE when the target is identified by its character position and length. REPLACE does not search for a particular old-text sequence, so it suits fixed-format strings better than variable text.

With AB-1234 in A2, the following formula replaces three characters starting at position 4 and returns AB-XYZ4: =REPLACE(A2,4,3,"XYZ")

For a one-time change to existing cell values, Home > Find & Select > Replace opens the replacement controls in Excel for Microsoft 365 on Windows. Find and Replace changes selected cells or the worksheet directly, while SUBSTITUTE creates formula results and preserves the source values. Use the formula when the change must remain repeatable; use the dialog when you intend to overwrite matching content after reviewing the scope.

Common problems and reliable workflow

A formula that returns the original text often means the old-text argument does not exactly match the cell contents. Look for leading or trailing spaces, a different dash character, or a nonbreaking space. Copying a character from a source cell into the formula’s search text can help identify a visually similar but distinct character.

If Excel displays the formula instead of calculating it, check that the destination cell is not formatted as Text. Change the format to General, then re-enter the formula. Also confirm that the formula begins with an equals sign and that the argument separators match the installation’s regional settings.

Before applying a transformation to a full column, use a short, deliberate workflow:

  1. Keep an untouched copy of the source data or work in a new output column.
  2. Test the formula on examples with common and unusual values, including blanks and repeated matches.
  3. Verify whether every occurrence should change or only one occurrence should change.
  4. Review the output for unintended changes before using it in a report, import, or lookup.
  5. If you need static results, copy the completed output and use Paste Special > Values to replace formulas with their current results.

That last step is optional. Keeping formulas means the output can recalculate when the source changes; converting to values makes the result independent of the source formula. Choose based on whether the transformation needs to stay live.

Frequently Asked Questions

Does SUBSTITUTE replace all matches in Excel?
Yes. If you omit the optional instance number, SUBSTITUTE replaces every occurrence of the specified old text in the source string.

How do I replace only the first occurrence?
Supply 1 as the fourth argument. Excel numbers matching occurrences from left to right, so 1 identifies the first match.

Can SUBSTITUTE remove text instead of replacing it?
Yes. Use an empty string as the new text. The matching text is removed, while the rest of the cell’s text remains.

Is SUBSTITUTE case-sensitive?
Yes. Uppercase and lowercase letters are treated as different characters, so an old-text value of Cat does not match cat.

Why does SUBSTITUTE leave a character unchanged?
The searched text may differ from the cell’s character even if it looks the same, or the capitalization may not match. Check punctuation, spaces, and character types, then test the formula on a sample value.

Does SUBSTITUTE change the original cell?
No. It returns the modified text where the formula is entered. The source cell remains unchanged unless you later copy the result and paste it as values over the source.

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.