How to replace text in Excel with a formula

Replace every match with SUBSTITUTE

SUBSTITUTE has three required arguments: the text, the text to find, and what to put in its place. It replaces every match it finds, and it returns a new value in the formula cell. The original stays as it was.

Here dotted phone numbers become dashed ones. Fill the formula down to fix the whole column.

Replace only the first, second or nth match

The optional fourth argument, instance_num, tells SUBSTITUTE which match to change. The product name below mentions 2025 twice, but only the first one is the product name. Changing just instance 1 keeps the note in brackets intact.

There is no built-in way to say "the last match". Count the matches with LEN(A2)-LEN(SUBSTITUTE(A2,"x","")) and pass that number as instance_num.

Replace several different things at once

SUBSTITUTE handles one find-and-replace pair. For more, nest them: the inner SUBSTITUTE runs first and the outer one works on its result. Here "St" and "Apt" are expanded in one formula.

Nesting gets hard to read after four or five levels. For long lists of replacements, keep the pairs in a two-column table and use Find and Replace, or in Excel 365 a LET or LAMBDA formula.

Change a prefix (and why the comma matters)

Include enough of the surrounding text to make the match unique. Replacing "OLD-" rather than "OLD" means a code like OLD-4471 changes, but a product called "Old Oak Table" would not be touched, partly because SUBSTITUTE is case-sensitive and partly because the dash must be there too.

The same idea is why the address example searches for "St," with the comma: a bare "St" would also change the "St" in a street name like "Stone Rd".

REPLACE: change text by position

Excel also has a REPLACE function. It does not search for anything. You tell it where to start and how many characters to overwrite: =REPLACE(D2,1,3,"NEW") replaces the first 3 characters and returns "NEW-4471". Use it for fixed-layout codes where the position matters but the content varies.

Use SUBSTITUTE when you know what the text says, and REPLACE when you know where it is.

Replace in the cells themselves: Find and Replace

If you want the cells changed, not a new column, use Find and Replace. It is not case-sensitive by default, and it supports wildcards: * matches any run of characters and ? matches one. To find a real asterisk or question mark, put a tilde in front: ~*.

1. Select the cells to change (or one cell to search the whole sheet).
2. Press Ctrl+H, or go to Home > Find & Select > Replace.
3. Type the text in Find what and the new text in Replace with.
4. Click Options to set Match case or Match entire cell contents if needed.
5. Click Replace All, then check the count Excel reports.