How to Find and Replace in Excel Without Errors

Knowing how to find and replace in Excel means swapping one value for another across a selection, a sheet, or an entire workbook in a single pass. Unlike editing cells by hand, it changes every match at once — which is exactly how it quietly damages a file. The options are what keep it safe.

Open Find and Replace and set the scope

Scope is the first decision, and the one people skip. Excel replaces inside whatever you gave it, so define that before you type anything.

Press Ctrl+H for the Replace tab, or Ctrl+F for Find. Microsoft documents Control+F or Command+F for Find on a Mac, with Control+H for Replace [1]. The menu route is Home > Editing > Find & Select > Replace, which lands in the same dialog.

Then choose how wide the search goes:

• Select a range first. Highlight a column or block of cells and Excel confines the search to that selection. This is the single most effective guard against collateral damage.
• Within. Set it to Sheet or Workbook [1]. Workbook is the option that turns a five-cell fix into a hundred-cell surprise, so leave it on Sheet unless you mean it. In Excel for the web the same control offers Selection, Sheet, or Workbook [1].
• Search. By Rows is the default; By Columns changes the order matches are found in [1]. On the web it reads Down or Up instead [1].

One asymmetry is worth knowing before you start. The Look in box offers Formulas, Values, Notes, and Comments — but Microsoft notes that the Replace tab supports only Formulas [1]. You can find a value that a formula produced; you cannot replace it. Replacement always writes to what the cell actually contains, which for a calculated cell is the formula, not the number you see.

The options that decide what actually changes

Click Options and the dialog doubles in size. The four controls hiding there are the difference between a clean fix and a corrupted column.

Match entire cell contents deserves the most attention, because its default (off) is the cause of most Find-and-Replace accidents. With it off, replacing Inc with Incorporated also rewrites Incident, Include, and Vincent. With it on, only a cell holding exactly Inc changes.

The Format button is the least-known and genuinely useful one. Choose Format From Cell lets you click a badly formatted cell and find every other cell that matches it — a fast way to hunt down a single red-filled row that somebody left in a report.

Before committing, click Find All rather than Replace All. Excel lists every match with its cell address, and the count at the bottom tells you the size of what you are about to change. If the number is wildly higher than you expected, your search string is too loose.

Wildcards and the tilde that escapes them

Wildcards turn one search into a pattern, and Excel supports exactly three characters.

Microsoft documents them as a question mark for any single character, an asterisk for any number of characters, and a tilde to escape the other two [2]:

• sm?th finds smith and smyth [2]
• *east finds Northeast and Southeast [2]
• fy06~? finds the literal text fy06? [2]

They apply to Excel for Microsoft 365, 2024, 2021, 2019, and 2016, and work in text filters and in conditional formatting rules that use the "cells that contain specific text" criteria [2].

The escape rule is what trips people up. A question mark and an asterisk are common in real data — product codes, footnote markers, imported placeholders like *missing*. Searching for them without a leading tilde matches almost everything in the column, and Replace All then empties it. Write ~* for a real asterisk, ~? for a real question mark, and ~~ for a tilde.

What the Find and Replace dialog does not have is regular expressions. There is no capture group, no character class, no way to say "any four digits". Google Sheets does offer this: its find-and-replace panel includes a "Search using regular expressions" checkbox alongside Match case, Match entire cell contents, and "Also search within formulas" [4]. If your fix genuinely needs a pattern rather than a wildcard, Sheets is the spreadsheet with the feature — at the cost of putting the file on Google's servers first. Excel's own answer is a worksheet function rather than a dialog option, and REGEXREPLACE is covered further down.

If the file is already in Sheets and the real problem is repeated rows rather than wrong text, deduplicating a list in Google Sheets is a separate job with its own trap: the matching is only as good as the whitespace clean-up you run first.

When a formula beats Replace All

Replace All overwrites your source data. A formula does not, and that difference matters on any file you cannot re-download.

SUBSTITUTE is the direct equivalent. Microsoft gives the syntax as SUBSTITUTE(text, old_text, new_text, [instance_num]), and documents that when instance_num is supplied only that occurrence is replaced, while omitting it changes every occurrence of old_text in the string [3]. So =SUBSTITUTE(A2,"Ltd","Limited") fixes every instance in the cell, and =SUBSTITUTE(A2,"-","",2) removes only the second hyphen — something the dialog cannot express at all.

Neither function collapses spelling variants, though. SUBSTITUTE needs the exact string you are replacing, so Acme Inc, ACME Incorporated, and Acme, Inc. stay three values until you write three rules.

REPLACE is the positional sibling. Microsoft's own guidance draws the line clearly: use SUBSTITUTE to replace specific text in a string, and REPLACE to replace whatever text sits at a specific location [3]. Stripping a fixed three-character prefix from every ID is a REPLACE job; fixing a company suffix is a SUBSTITUTE job.

The trade-off is the same one that applies to combining columns with a formula: the result is a live calculation in a second column, not a value. Before you export, copy the formula column and use Paste Special > Values — otherwise the CSV inherits either the formula text or a column of #REF! errors once the source is deleted. The same trap catches a formula-based split of a full-name column, which is why that guide converts to values before the original column goes. Once the column holds values, an Excel to CSV converter writes exactly what each cell displays, so nothing recalculates on the way out. If the column you are building is a join of two others, you can skip the formula layer altogether: combining the columns in the CSV itself writes plain values, with blank cells skipped and no Paste Special step.

Replace many values at once with a lookup table

The dialog takes one Find/Replace pair per run. Twenty spelling variants means twenty passes, and each pass is another chance to hit a partial match you did not intend.

For two or three substring swaps, nest SUBSTITUTE: =SUBSTITUTE(SUBSTITUTE(A2,"Ltd","Limited"),"Co.","Company"). Each layer runs on the output of the one inside it [3]. Past three layers the formula is unreadable and the order of the swaps starts to matter.

When the job is replacing whole values — spelling variants to one canonical name, state names to two-letter codes, old product codes to new ones — move the pairs into a two-column mapping table instead. Put the values to find in one column, their replacements beside them, and look each cell up:

=XLOOKUP(A2, Map!A:A, Map!B:B, A2)

The fourth argument is what makes this a find-and-replace rather than a lookup. Microsoft documents if_not_found as the value returned "where a valid match is not found" [7], so passing A2 back means every value missing from the map comes through unchanged instead of turning into #N/A. The default match mode is exact [7], which gives you the protection Match entire cell contents gives the dialog: Acme Inc in the map will not rewrite Acme Incident Report.

That is also the answer to the variants SUBSTITUTE cannot collapse. Acme Inc, ACME Incorporated, and Acme, Inc. become three rows in the map, all pointing at one canonical spelling, and the table doubles as a record of every decision you made. Microsoft notes XLOOKUP is not available in Excel 2016 or 2019 [7]; there, =IFERROR(VLOOKUP(A2, Map!A:B, 2, FALSE), A2) does the same job.

Two cautions carry over from the rest of this guide. An exact match fails on a trailing or non-breaking space, so clean the column before the lookup runs. And the result is a formula column — flatten it with Paste Special > Values before the source goes.

Why Replace All goes wrong on a fresh export

Most Find-and-Replace disasters happen on a file that arrived that morning from a CRM, a payroll system, or a survey tool. Four failure modes account for nearly all of them.

Partial matches. Covered above, and the reason Match entire cell contents exists. Test with Find All first.

Hidden characters that are not what they look like. A value that appears to contain a space may hold a non-breaking space, character 160, which arrives with anything copied out of a web page or PDF. Typing a normal space in the Find box will not match it. That whole class of problem, and the SUBSTITUTE-with-CHAR(160) fix, is covered in the guide to removing spaces in Excel.

Excel reformatting values on the way in. This one happens before you open the dialog. Opening a CSV in Excel converts date-like strings into dates and drops leading zeros from ZIP codes and phone numbers. Your replacement then runs against data Excel has already altered, and no undo step recovers the original digits. Excel's Automatic Data Conversion panel switches part of that off, and how to open a CSV in Excel without the parser rewriting it covers both the panel and the import route that makes the fix per-file rather than per-machine. The only reliable way around it is not to open the file in a spreadsheet at all — swapping the text directly in the CSV leaves every value as the characters it already was, because nothing is parsing them as numbers or dates on the way in. If the work genuinely has to happen in a workbook, convert the CSV to Excel rather than double-clicking it, so each column arrives as a written cell value instead of something Excel guessed a type for.

Replacing inside formulas without meaning to. Because the Replace tab writes to Formulas [1], a search for A5 will rewrite references in every formula containing it. That is a deliberate feature for repointing a model, and a bad accident when you meant to fix a text label.

The habit that prevents all four: work on a copy, narrow the scope to a selection, run Find All to see the count, then replace.

Replace the characters you cannot type

The worst offenders in a fresh export are often characters with no key on the keyboard: line breaks inside cells, non-breaking spaces from a web page, and values that differ only by formatting. Find and Replace handles all three once you know how to enter them.

Line breaks inside a cell. A cell holds several lines because Alt+Enter — Control+Option+Return on a Mac — inserts a line break where the cursor sits [5]. Those breaks survive into a CSV export, where they split one record across several rows and the importer rejects the file. To clear them, click into the Find what box and press Ctrl+J. The box still looks empty, but it now holds the line-feed character. Leave Replace with blank to delete the breaks, or type one space to keep the words apart. Press Ctrl+J once only — a second press stacks two characters and the search stops matching anything.

Non-breaking spaces. Text pasted from a browser carries character 160, which looks like a space and behaves like one to the eye, but TRIM leaves it alone. In the Find what box, hold Alt and type 0160 on the numeric keypad, then replace it with a normal space. The walkthrough of TRIM and the spaces it misses covers why this one character defeats the usual cleanup.

Values that differ only in format. Find and Replace can match on appearance rather than content. Select Format, then "make your selections in the Find Format dialog box" [1]. Microsoft also notes you can "delete any criteria in the Find what box, and then select a specific cell format as an example" [1] — which is how you strip a stray fill colour or a currency format applied to only some rows. Clear the format criteria when you are done, because Excel remembers them and the next plain search will report no matches on text that is plainly there.

Use REGEXREPLACE when a wildcard cannot describe the pattern

A wildcard can say "any characters". It cannot say "any four digits", "only if the value ends in a letter", or "the second hyphen but not the first". For those, Excel now has a function the dialog does not expose.

REGEXREPLACE takes REGEXREPLACE(text, pattern, replacement, [occurrence], [case_sensitivity]), and Microsoft lists it for Excel for Microsoft 365 and Excel for Microsoft 365 for Mac. The patterns use the PCRE2 flavour of regex, which is the same dialect most programming languages ship, so a pattern you find online will usually work unchanged.

Two arguments do the work the dialog cannot:

• occurrence defaults to 0, which replaces every instance. Pass 2 to change only the second match, or a negative number to count from the end of the string.
• case_sensitivity defaults to case-sensitive. Pass 1 to ignore case, which is the regex equivalent of clearing Match case.

The patterns that earn their keep on an export are short ones. =REGEXREPLACE(A2,"\D","") strips every non-digit from a phone column in one pass, where the dialog needs a separate replacement for each of brackets, spaces, dots, and dashes. =REGEXREPLACE(A2,"\s+"," ") collapses runs of any whitespace — including the tabs and line breaks TRIM handles inconsistently — down to one space.

Three cautions. It is a formula, so the same rule from the SUBSTITUTE section applies: Paste Special > Values before exporting, or the CSV carries formulas or #REF! errors. The availability list is narrow — a perpetual-licence Excel 2021 or 2024 does not have it, so a workbook that relies on it will not open usefully on a colleague's machine. And a loose pattern destroys a column faster than a loose Find and Replace ever could, silently and everywhere at once, so test it in a helper column against twenty rows before filling it down twenty thousand.

For a phone column specifically, stripping the non-digits is only half the job — you still have no country codes, which is why a dedicated E.164 formatter beats any pattern you can write. And if the file is a CSV rather than a workbook, the pattern question is moot: running the swap on the text itself skips both the regex dialect and Excel's type guessing.

Do the same swap on a CSV without Excel

If the file is a CSV and its destination is an importer rather than a spreadsheet, Excel is a detour that adds risk.

Importers are also stricter than spreadsheets about exact values. A CRM dropdown accepts only the options it already defines, so a whole-cell replace is often the fix that makes a file import cleanly — the guide to preparing a CSV for a HubSpot contact import shows the pattern on lifecycle and checkbox columns.

A CSV file is plain text. Nothing in it needs a formula engine, and every conversion Excel applies on open — dates, scientific notation, dropped leading zeros — is a change you did not ask for. Sigmera's find and replace tool for CSV files works on the text directly: pick a column, set match case or whole-cell matching, see the affected rows before you commit, and download the corrected file. It runs entirely in your browser, so a file full of customer names and phone numbers never reaches a server and stays GDPR-safe by default. Phone columns are the common case where find-and-replace stops being enough: stripping dashes and brackets one pattern at a time still leaves you without country codes, so reach for a dedicated phone number formatter instead of chaining replacements. Each of those jobs has its own tool in the same browser-based data cleaning software, and none of them ask you to upload the file first.

Order matters if the file needs more than one fix. Replace stray characters first, standardise formats next, and deduplicate last — matching two spellings of the same record only works after both have been normalised. If that last pass is the one you are stuck on, how to remove duplicates in Excel walks through the Data-tab dialog and the COUNTIF formulas that flag repeats before you delete them. For a file that never needs to enter a spreadsheet at all, the CSV duplicate remover does the same job on the raw export and shows the flagged rows before anything is dropped. The rest of the data cleansing tools follow the same in-browser model, and previewing every result is free and unlimited; the download counter only ticks when you export the finished file.