Excel Find and Remove Text: Replace, Formulas, and Tips

Formulas & functions · Updated November 19, 2026

To find and remove text in Excel, press Ctrl+H, enter the text in Find what, leave Replace with empty, and choose Replace All. For a reversible result that keeps the original data, use the SUBSTITUTE function in a new column instead.

Use Find and Replace to remove text directly

Find and Replace is the quickest choice when you want to change the cells themselves. In Excel for Microsoft 365 on Windows, select the range you want to edit, then choose Home > Find & Select > Replace, or press Ctrl+H. Type the characters to remove in Find what and leave Replace with blank. Choose Replace All to remove every match in the selected scope, or Replace to review matches individually.

For example, if cells contain “North Region – archived” and you enter “ – archived” as the search text, replacing it with nothing leaves “North Region.” Searching for the full phrase, including its leading space and hyphen, is safer than searching for “archived” alone if that word might appear in a legitimate name or note.

  • Save a copy or duplicate the worksheet first if the change is difficult to reverse.
  • Select the intended cells before replacing when you do not want to affect the whole sheet.
  • Check the replacement count Excel reports and inspect several changed cells.
  • Use Ctrl+Z immediately if the result is wrong; after other actions or saving and closing, undo may not be available.

On Excel for Mac, use the Find and Replace command from the Edit menu or the Home ribbon, depending on the version; shortcut behavior can vary with keyboard settings. In Excel for the web, open Find and Replace from the Home tab. The key idea is the same: define the search text, leave the replacement empty, and check whether the search applies to the selection, sheet, or workbook.

Set the search scope and matching options

A replacement can affect more cells than expected if its scope is broader than the data you meant to clean. Selecting a range first is a useful guardrail, but check the dialog’s options as well. Excel can search within the current sheet or the entire workbook. A workbook-wide replacement may alter labels, notes, or other sheets that happen to contain the same text.

Expand the Find and Replace options if you need to control how Excel interprets the search. Depending on the dialog and version, options include looking in formulas or values, matching letter case, and matching the entire cell contents. Match case matters when “ID” and “id” should be treated differently. Match entire cell contents is useful for removing a cell only when its whole value is the target, but it will not find that text embedded in a longer string.

Excel’s search can also use wildcard characters. An asterisk (*) represents any sequence of characters, while a question mark (?) represents one character. To search for a literal asterisk, question mark, or tilde, precede it with a tilde: ~*, ~?, or ~~. This matters when cleaning filenames, codes, or text that contains punctuation; otherwise, a wildcard search can match many unintended values.

Remove text with a formula while preserving the source

A formula is better when you want to keep the original data, review the cleaned result, or apply the same rule to many rows. It returns a result in another cell instead of changing the source. With the text to clean in A2, enter this in B2:

=SUBSTITUTE(A2," / pending","")

If A2 contains “INV-204 / pending,” the result in B2 is “INV-204.” Fill the formula down to process more rows. When the results are approved, copy the formula results and use Paste Special > Values if you need fixed text rather than formulas. Keep the source column until you have checked the output.

SUBSTITUTE replaces every occurrence of the specified text by default. For instance, with “A-7-A” in A2, =SUBSTITUTE(A2,"A","X") returns “X-7-X.” SUBSTITUTE is case-sensitive, so searching for “a” does not remove an uppercase “A.” To replace only a particular occurrence, you can supply the optional occurrence number. With “red, blue, red” in A2, =SUBSTITUTE(A2,"red","green",2) returns “red, blue, green.”

For a simple, fixed substring, SUBSTITUTE is usually more suitable than Find and Replace when you want a non-destructive check. It does not interpret * and ? as wildcards: it searches for the characters you specify. If the unwanted text varies from row to row or depends on position, a different text function or a deliberate cleanup process may be needed.

Clean spaces and unwanted characters

Sometimes the visible text is not the whole problem. Data copied from websites or other systems may contain repeated spaces, line breaks, or nonbreaking spaces that look like ordinary spaces but behave differently. Removing a word can leave an extra space behind, so inspect the result rather than assuming that deleting the target phrase also produces tidy text.

With text in A2, =TRIM(SUBSTITUTE(A2," pending","")) removes the phrase “ pending” and trims leading and trailing ordinary spaces, while reducing repeated ordinary spaces between words to single spaces. For example, if A2 is “INV-204 pending ”, the result is “INV-204.” TRIM is not a universal whitespace cleaner: it does not remove every nonprinting character, and it can change intentional multiple spaces.

For a nonbreaking space, Excel’s character value is often represented by CHAR(160). With imported text in A2, =TRIM(SUBSTITUTE(A2,CHAR(160)," ")) changes those characters to regular spaces and then trims the result. If unwanted line breaks or other characters remain, identify what the source contains before removing characters broadly. CLEAN can remove certain nonprinting characters, but applying cleanup functions indiscriminately may remove content that matters.

Choose the right method for the job

Find and Replace and formulas solve related but different problems. Find and Replace edits existing cells, making it convenient for a one-time correction. SUBSTITUTE creates a calculated version of the text and leaves the original untouched, which is safer for review and repeatable work. Neither method is automatically best; choose based on whether you need a permanent edit, an auditable result, or a reusable transformation.

  • Use Replace All for a known, consistent phrase that should disappear from a defined range or sheet.
  • Use SUBSTITUTE when you want to compare before-and-after values or fill a rule down a column.
  • Use Flash Fill when you can show Excel a pattern of desired outputs and the task is a one-time transformation. Find Flash Fill under Data > Flash Fill on Windows, or use Ctrl+E. Review its results because pattern recognition may not handle every exception.
  • Use Power Query for repeatable imports and cleaning steps that you want to refresh. In the Power Query Editor, select the relevant column and use Transform > Replace Values; this is more setup than a quick cell replacement but can make recurring data preparation easier to audit.

Flash Fill is not a live formula: if source values change later, its generated results do not automatically recalculate from those values. Power Query also operates on a query output rather than directly editing the original source cells. These distinctions are useful when deciding whether you are cleaning a one-off list or building a process you will run again.

Common problems and how to avoid them

Replace All changed too much. The search may have covered the whole sheet or workbook, or the search phrase may have been too short. Undo right away if possible, then select a narrower range and include enough surrounding characters to identify the unwanted text precisely.

Excel says it cannot find the text. Check for leading or trailing spaces, different punctuation, line breaks, or a nonbreaking space. Also check whether the dialog is looking in formulas or values and whether a case-sensitive or entire-cell option is enabled. A visible cell value can differ from the underlying formula or characters Excel searches.

Only some cells changed. The text may have different capitalization, spacing, or spelling in different rows. A formula using SUBSTITUTE is case-sensitive, while a Find and Replace search can be configured to match case. Standardize the source or handle each genuine variation deliberately rather than broadening the search without checking the consequences.

A formula result is not the text you expected. Confirm that the formula points to the right source cell and that the search string includes exactly the characters you intend to remove. If you see the formula itself in the cell, check that the cell is not formatted as Text and that Show Formulas is not turned on. If you need fixed outputs, copy the results and paste values after reviewing them.

Nothing seems to happen. A replacement with a blank value may be hard to notice if the match is only part of a longer cell. Check the reported number of replacements and inspect the relevant cells. Also confirm that the worksheet is not protected in a way that prevents editing.

Frequently Asked Questions

How do I remove a word from every cell in Excel?
Press Ctrl+H, enter the word in Find what, leave Replace with empty, and choose Replace All. Select the relevant range first and review the result, especially if the word can occur as part of other text.

How do I remove text but keep the original column?
Use a formula in a neighboring column, such as =SUBSTITUTE(A2,"-old","") when A2 contains the text and “-old” is the exact substring to remove. Fill it down and keep the source column until you have verified the results.

Can Excel remove text before or after a character?
For a fixed, known substring, SUBSTITUTE can remove that substring. If the portion to remove varies according to a delimiter or position, the appropriate method depends on the Excel version and the exact pattern. Check a few representative inputs first; one broad replacement rule may not fit every row.

Does Find and Replace remove text from formulas too?
It can search formulas or displayed values, depending on the search options. Be cautious when editing formulas: replacing text inside a formula can change its logic or make it invalid. For a visible text result, check the selected “Look in” setting before replacing.

How do I remove extra spaces after deleting text?
Use TRIM on the result when ordinary spaces are the issue. For example, with the source in A2, =TRIM(SUBSTITUTE(A2," pending","")) removes that phrase and tidies ordinary spaces. Nonbreaking spaces may need to be converted with SUBSTITUTE and CHAR(160) first.

Can I undo Replace All?
Usually, Ctrl+Z on Windows reverses a recent replacement, and Command+Z is the Mac equivalent. Undo availability depends on what you have done since the replacement and whether the workbook remains open, so save a copy before a broad or high-impact change.

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.