How Replace Works in Excel: Find and Replace

How-to basics · Updated November 20, 2026

In Excel, “replace” can mean the REPLACE function, which swaps characters at a specified position in text, or the Find and Replace command, which searches cells for content and changes matches. Use the function for a repeatable formula-based edit; use Find and Replace for a direct change to selected cells or a worksheet.

Two different ways to replace content

The distinction matters because these tools do not identify text in the same way. The REPLACE function counts characters from the start of a text string, then overwrites a specified number of them. Find and Replace searches for content you specify, wherever it occurs in the cells covered by the search.

  • REPLACE function: Best when the characters to change always occupy known positions, such as the year in a consistently formatted code.
  • Find and Replace command: Best for correcting a word, changing a repeated label, or editing a value directly in many cells.
  • SUBSTITUTE function: Often a better formula choice when you want to replace a particular piece of text wherever it appears, rather than replacing characters by position.

These methods can produce similar-looking results, but their rules differ. A formula creates a result in a cell and usually leaves the source cell unchanged. Find and Replace edits matching cell content in place, so it can alter source data immediately.

How the REPLACE function works

The syntax is REPLACE(old_text, start_num, num_chars, new_text). old_text is the original text or a cell reference containing it. start_num gives the position of the first character to change, counting from 1. num_chars tells Excel how many characters to remove, and new_text supplies the characters to put in their place.

For a cell layout with the text INV-2024-017 in A2, this formula replaces four characters beginning at position 5:

=REPLACE(A2,5,4,"2025")

The result is INV-2025-017. The first four characters, INV-, remain untouched; Excel replaces the four-character year and preserves the remaining -017. The position is counted across the complete string, including punctuation and spaces.

REPLACE does not search for a pattern such as “the year” or “the text after the first hyphen.” It follows the positions and character count you give it. That makes it predictable when all entries share a format, but risky when string lengths or layouts vary.

Insert or delete characters with a formula

You can also use REPLACE to insert text or remove characters. To insert, set num_chars to 0: Excel replaces nothing and puts the new text at the specified position. For a cell layout with INV-2024-017 in A2, this formula adds a prefix before the first character:

=REPLACE(A2,1,0,"US-")

The result is US-INV-2024-017. To delete characters, use an empty string for new_text. For a cell layout with INV-2024-017 in A2, this formula removes the four-character year beginning at position 5, leaving the hyphens in place:

=REPLACE(A2,5,4,"")

The result is INV--017. That result illustrates why you should check what surrounds a deleted section; if the goal is to remove every hyphen as well, a text-based replacement may be more suitable.

If start_num is greater than the number of characters in the original text, Excel appends the new text to the end. A start position below 1 or a negative num_chars produces a #VALUE! error. If num_chars extends beyond the end of the text, Excel replaces the characters from the start position through the end.

Using Find and Replace in Excel

In Excel for Microsoft 365 on Windows, select the range you want to edit, then choose Home > Find & Select > Replace. You can also press Ctrl+H. Enter the existing content in Find what and the new content in Replace with. Choose Replace to review matches one at a time, or Replace All to change every match within the search scope.

  1. Select a range first if you want to limit the change to those cells. If no range is selected, Excel searches the worksheet.
  2. Open the Replace tab of the Find and Replace dialog and enter the text to find and the replacement text.
  3. Check the search options if the results need to be limited by case, whole-cell matches, or where Excel looks for content.
  4. Use Replace to inspect matches individually, or Replace All when you are confident about the scope.
  5. Review Excel’s count of replacements and inspect a few affected cells. Use Ctrl+Z promptly if an unintended change was made.

On Excel for Mac, use the Replace command from the Home tab’s Find & Select controls; keyboard shortcuts can vary with keyboard settings and Excel version. Excel for the web also has Find and Replace, though its available options and dialog can differ from the Windows desktop application. If a particular search option is missing, use the desktop version rather than assuming the web interface supports the same controls.

Search options, wildcards, and scope

Find and Replace can do more than match a simple word. The dialog’s options let you control how Excel searches. Match case distinguishes uppercase and lowercase letters, while Match entire cell contents restricts a match to cells whose full contents equal the search text. Without the whole-cell option, a search for North can match that text inside a longer entry such as Northwest.

Pay attention to where Excel searches. The Within option can determine whether the search applies to the current sheet or the workbook, and Look in controls whether Excel searches formulas, values, or notes/comments where those options are available. Searching formulas can find text that appears in a formula’s expression; searching values targets what cells display or return. This difference is useful when a displayed result is correct but the underlying formula contains an old label—or the reverse.

In Find and Replace, the asterisk (*) can stand for any sequence of characters, and the question mark (?) can stand for one character. A tilde (~) lets you search for a literal wildcard character, such as ~* to find an asterisk itself. Wildcards can be powerful, but a broad pattern can match more cells than expected. Try a narrow search, inspect matches, and use Replace rather than Replace All when the pattern is uncertain.

REPLACE compared with SUBSTITUTE

Use REPLACE when the location and length of the text to change are known. Use SUBSTITUTE when the text to change is known but its position may vary. For example, removing hyphens regardless of where they occur is a natural job for SUBSTITUTE, because it looks for the hyphen rather than relying on character positions.

For a cell layout with INV-2024-017 in A2, this formula removes every hyphen:

=SUBSTITUTE(A2,"-","")

The result is INV2024017. SUBSTITUTE can also target a particular occurrence by adding its optional instance number. Both functions return a text result; neither changes the original cell by itself. A key difference is that SUBSTITUTE is case-sensitive, while Find and Replace has a separate Match case option. Choose the method that reflects how the data varies, not simply the one with the shorter formula.

Results, cell types, and common mistakes

A formula’s result appears in the cell containing the formula, so put it in a spare column if you need to preserve the original data. Formulas update when their referenced cells change. If you need to overwrite the original values with the formula results, copy the results and use Paste Special > Values over the original cells only after checking them. Keep a copy of important source data before a large transformation.

Although REPLACE manipulates text, Excel may display numbers without making it obvious whether they are stored as text or numeric values. Codes with leading zeros, product IDs, and postal codes often need to remain text; converting them to numbers can remove meaningful zeros. After a formula or a Find and Replace operation, check representative cells and the formula bar. Do not assume that a result that looks right has the data type needed for later sorting, matching, or calculations.

  • Wrong characters changed: Recount positions from the first character, including spaces, punctuation, and leading characters.
  • Only some entries work: Check whether the strings have different lengths or inconsistent formats. Position-based replacement assumes predictable layouts.
  • Too many cells changed: Undo immediately, then select the intended range and review the search scope and wildcard settings before trying again.
  • A formula shows an error: Check for a start position below 1, a negative character count, or an incorrect cell reference.
  • A replacement appears not to work: Check whether the search is case-sensitive, whether it is looking in formulas or values, and whether the replacement text is actually present.

Choosing the safest method

For a one-time correction in a known, limited range, Find and Replace is usually quickest. For a repeatable cleanup that should update when source data changes, use a formula. For fixed-position codes, REPLACE is direct; for a known substring that can occur anywhere, SUBSTITUTE is usually safer. When the format is irregular, neither positional formulas nor broad replacement patterns should be applied without inspecting the data first.

Before using Replace All across a workbook, consider whether the search term occurs in formulas, labels, or unrelated records. Start with a selected range, test a few individual replacements, and make a backup if the change is difficult to reconstruct. Ctrl+Z can undo a recent Find and Replace action in Excel, but it is not a substitute for a backup if you continue editing or close the workbook.

Frequently Asked Questions

What is the difference between REPLACE and Find and Replace in Excel?
REPLACE is a worksheet function that returns text with a specified number of characters changed at a given position. Find and Replace is a command that searches cells for matching content and edits those cells directly.

How do I replace part of a text string in Excel?
Use REPLACE if you know the character position and number of characters, or SUBSTITUTE if you know the text to locate. For a one-time edit, use Home > Find & Select > Replace.

Can REPLACE change a word wherever it appears?
Not by searching for that word. REPLACE works by position and character count. Use SUBSTITUTE to replace a specified text fragment wherever it occurs in the cell’s text.

How do I replace text in only selected cells?
Select the cells first, open Find and Replace, and run the replacement. Check the dialog’s scope and options before choosing Replace All, especially if the worksheet contains similar text elsewhere.

Can I undo Replace All?
Usually, you can press Ctrl+Z immediately after the operation in Excel for Windows. If you have continued working or the change cannot be undone, restore a saved copy or use a backup; do not rely on undo as your only protection.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

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

Spotted an error in this guide? Let us know.