Excel Find and Replace: What It Returns and How It Works

Excel’s Find and Replace command does not return a value to a worksheet cell: Find locates matching content, while Replace changes it. If you mean the FIND worksheet function, it returns the starting position of one text string inside another as a number, or #VALUE! if the text is not found.

Find and Replace versus the FIND function

The similar names refer to different tools. Find and Replace is an interactive editing command that searches the workbook and can change matching entries. The FIND function is a formula you place in a cell to calculate where a piece of text occurs. Neither the dialog’s Find button nor its Replace button supplies a formula result to the active cell.

For example, searching for “8472” in a worksheet can select a cell containing that number as part of a longer entry. Replacing “8472” with “9000” edits the matching cell content. By contrast, a FIND formula looking for “8472” in “Order 8472” returns the number 7, because the first character of the match is in position seven.

This distinction matters when you want to inspect or transform data without manually changing cells. Use the dialog for direct edits; use a formula when the location of the text should be calculated, displayed, or used in another calculation.

Open Find and Replace in Excel

In Excel for Microsoft 365 on Windows, press Ctrl+H to open Replace. You can also go to Home > Find & Select > Replace. To search without replacing, press Ctrl+F or choose Home > Find & Select > Find. On Mac, use Edit > Find > Replace or Edit > Find > Find; available keyboard shortcuts can depend on the keyboard and Excel setup. Excel for the web offers Find and Replace too, though its interface and available options can differ from the desktop app.

Enter the text to locate in Find what.
If you want to change it, enter the replacement in Replace with.
Choose Find Next to move through matches, Find All to list matches, Replace to change the current match, or Replace All to change every match in the selected scope.

Find Next selects a matching cell; it does not copy the match into a cell or create a result formula. Find All displays matching locations in the dialog so you can inspect them. After Replace All, Excel reports how many replacements it made. If no match is found, the search does not change the worksheet.

Choose the search scope and options

Before a broad search, check where Excel is looking. In the desktop Find and Replace dialog, Within can set the search to the current sheet or the workbook. A sheet-level search is often safer when you know which tab contains the data; a workbook-level search helps when a term may appear on several sheets.

The Search setting controls whether Excel searches by rows or columns. The Look in setting can affect whether it searches formulas, values, or notes. This is important when a cell displays a calculated result: searching for the displayed text may behave differently from searching the formula that produces it. Options such as Match case and Match entire cell contents narrow the results. For example, matching the entire cell prevents a search for “East” from also matching “Northeast.”

Use the dialog’s format options when the target is defined partly by formatting rather than just text. A format criterion can make a search more specific, but it can also explain why an apparently obvious text match is being missed. If results seem incomplete, clear any unwanted format criteria and check the scope and Look in setting before changing the search term.

How the FIND function returns a position

The syntax is FIND(find_text, within_text, [start_num]). The first argument is the text to locate; the second is the text or cell to search; the optional third argument sets the character position where Excel begins searching. Positions start at 1, not 0. FIND is case-sensitive and does not support wildcard characters.

With the text “Order 8472” in cell A2, this formula returns 7 because “8472” begins at the seventh character:

With “Order 8472” in A2, enter =FIND("8472",A2); the result is 7.

If A2 contains “Blueberry,” this formula starts looking at character 2 and returns the position of the lowercase b at character 5. The initial uppercase B does not count as a match because FIND distinguishes case:

With “Blueberry” in A2, enter =FIND("b",A2,2); the result is 5.

The result is a position, not the text that was found. A formula can use that position as an input to another text function, or you can display a message when there is no match. For example, with the product description in A2, this formula returns a position if “red” appears and the words “Not found” otherwise:

With a product description in A2, enter =IFERROR(FIND("red",A2),"Not found"); for “Red shirt” it returns Not found, because the search is case-sensitive.

FIND, SEARCH, REPLACE, and SUBSTITUTE

Choose the function based on the job, rather than assuming the worksheet function called FIND is the same as the Replace command:

FIND returns a text position and is case-sensitive. It does not treat * or ? as wildcards.
SEARCH also returns a position, but it is not case-sensitive and supports wildcards. It is useful when capitalization varies or a pattern is more appropriate than an exact string.
REPLACE changes text at a specified character position and for a specified number of characters.
SUBSTITUTE replaces a specified piece of text wherever it occurs, with an optional argument for replacing only a particular occurrence.

For a concrete positional example, suppose A2 contains “AB-1234.” With A2 holding that text, =REPLACE(A2,4,4,"5678") returns AB-5678: it replaces four characters beginning at position 4. This can be useful when the characters to change always occupy known positions, but it can damage inconsistent entries.

For a text-based replacement, suppose A2 contains “old red folder.” With that text in A2, =SUBSTITUTE(A2,"old","new") returns new red folder. Unlike Find and Replace, these functions return transformed text in the formula cell and leave the original cell unchanged. To make the result permanent, you can copy the formula results and use Paste Special > Values over the intended destination.

Common problems and unexpected results

A #VALUE! result from FIND usually means the exact case-sensitive text is absent, the starting position is invalid, or the search starts beyond the available text. Check for spaces, punctuation, different capitalization, and whether the referenced cell contains what you expect. If capitalization should not matter, consider SEARCH instead.

A dialog search that finds nothing may be limited to the active sheet when the match is elsewhere in the workbook. It may also be looking in formulas when you expected displayed values, or it may have retained a format criterion from an earlier search. Review those settings before concluding that the text is absent.

Wildcards can also create surprising matches in the dialog. In Excel searches, an asterisk can stand for a series of characters and a question mark can stand for one character. To search for a literal wildcard character, precede it with a tilde; for example, use ~* to find an actual asterisk. This wildcard behavior belongs to the search interface and is not how the FIND function works.

Another common mistake is replacing a short string that appears inside longer words. Replacing “cat” could affect “catalog” if the search is not restricted to entire-cell matches. Use Match entire cell contents when the whole cell must equal the search text, or inspect matches with Find All before making a large change.

Make replacements safely

Replace All is fast, but it can change formulas, values, or multiple sheets depending on the selected options. Before using it on important data, consider these precautions:

Save a copy of the workbook, especially before a workbook-wide replacement.
Set the correct sheet or workbook scope and review Match case and Match entire cell contents.
Use Find All first when you need to see which cells will be affected.
Start with Replace on one match if you are unsure whether the replacement is appropriate.
After replacing, review the reported count and inspect representative affected cells.

For ordinary worksheet edits, Ctrl+Z on Windows or Command+Z on Mac can undo a replacement, but do not rely on undo as a substitute for a backup. Undo history can be disrupted by other actions, and a saved copy makes recovery more reliable. If the source data must remain intact, use a formula such as SUBSTITUTE in a separate column and check the output before replacing the originals.

Frequently Asked Questions

Does Excel Find and Replace return a value?
No. Find selects or lists matching locations, while Replace edits matching content. To return a position in a cell, use the FIND worksheet function.

What does the Excel FIND function return?
It returns the starting character position of the text being searched for, counting from 1. It returns #VALUE! if the text is not found or the search position is invalid.

Why does FIND return #VALUE! when the text looks present?
FIND is case-sensitive, so “red” will not match “Red.” Extra spaces, punctuation, or a mistaken cell reference can also prevent a match. Use SEARCH if case should be ignored.

Can Find and Replace search the whole workbook?
Yes. In the desktop dialog, set Within to Workbook rather than Sheet. Check the scope before using Replace All because a workbook-wide operation can alter matches on multiple tabs.

How do I replace text in a formula without editing the original cell?
Use SUBSTITUTE in another cell to create a changed text result while preserving the source. Review the results, then copy and paste values only if you want to make those results permanent.

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.