To replace text in Excel for Microsoft 365 on Windows, press Ctrl+H, enter the text to find and its replacement, then choose Replace or Replace All. To create a changed copy while keeping the original text, use the SUBSTITUTE formula instead.
Choose the right way to replace text
Excel offers two main approaches. Find and Replace edits matching content in the worksheet; a formula returns changed text in a separate cell and leaves the source cell untouched. The best choice depends on whether the change should be permanent, repeatable, or easy to review before applying.
- Use Find and Replace for a direct edit, such as changing a product abbreviation throughout a selected sheet.
- Use SUBSTITUTE when you want to replace a particular word or character within text and keep the original data available.
- Use REPLACE when the characters to change are identified by their position in a string, rather than by a particular word.
- Use Power Query when you regularly import or refresh data and want the same replacement applied as part of a repeatable cleanup process.
Before a large replacement, save a copy of the workbook or test the change on a small selection. A replacement can affect more cells than expected, especially if the search scope includes the whole workbook or formulas.
Replace text with Find and Replace
On Windows, open the worksheet and choose Home > Find & Select > Replace, or press Ctrl+H. Type the existing text in Find what and the new text in Replace with. Select Replace to handle matches one at a time, or choose Replace All to change every match in the search scope. Excel reports how many replacements it made.
Replacing one match at a time is useful when the same word has different meanings in different cells. For instance, replacing “East” with “E” might be correct in a region-code column but wrong in a full address. A review of each match helps catch that distinction. Replace All is more efficient when the term is unambiguous and the scope is narrow.
For Mac, use Edit > Find > Replace from the menu. Keyboard shortcuts can vary with the keyboard and Excel setup, so the menu path is a dependable alternative. In Excel for the web, the available search controls and keyboard behavior can differ from desktop Excel; check the workbook’s search options before applying a wide replacement.
The Replace dialog includes options that can narrow or broaden the search. Depending on the dialog and version, you can set the search to the current sheet or the workbook, choose whether to search by rows or columns, select what to look in, and specify whether to match case or the entire cell contents. Use Options to reveal additional settings when they are hidden.
- Within: Sheet or Workbook: Choose Sheet for a local change. Workbook can reach other sheets, so confirm the scope before selecting Replace All.
- Match case: When enabled, uppercase and lowercase letters must match. This matters for names, codes, and identifiers that distinguish capitalization.
- Match entire cell contents: Use this when a cell must equal the search term, rather than merely contain it. It helps prevent changing a word embedded in a longer phrase.
- Look in: Check whether the search is looking at formulas, values, or other available cell content. If formulas are in scope, replacing text may alter a formula itself.
Use SUBSTITUTE to return changed text
SUBSTITUTE replaces matching text inside a text value. It is usually the clearest formula when you know the characters or word to change. With the source text in A2, enter the formula in another cell, such as B2, and fill it down for additional rows.
With Northwind Ltd. in A2, use =SUBSTITUTE(A2,"Ltd.","Limited"). The result is Northwind Limited. The formula changes every matching occurrence by default, but does not overwrite A2.
To replace just one occurrence, supply the optional fourth argument, instance_num. With red, red, blue in A2, =SUBSTITUTE(A2,"red","green",2) returns red, green, blue. The number 2 selects the second matching occurrence. If that argument is omitted, all occurrences of the old text are replaced.
SUBSTITUTE is case-sensitive: searching for "cat" does not match "Cat". If capitalization varies and you need a case-insensitive bulk edit, Find and Replace with Match case turned off may be more suitable. Always review sample results when case or punctuation is important.
Use REPLACE for changes by character position
REPLACE changes a specified number of characters starting at a position you provide. It does not search for a word. Its structure is REPLACE(old_text,start_num,num_chars,new_text), where the starting position is counted from the first character and the number of characters specifies how much to remove.
With AB-1234 in A2, use =REPLACE(A2,4,4,"5678"). Starting at character 4, the formula replaces four characters, so the result is AB-5678. This can help standardize a fixed-width code when the relevant part always occupies the same positions.
For a change based on a known substring, SUBSTITUTE is generally easier to maintain. If the text length or position varies, a positional REPLACE formula may change the wrong characters. Test it against short and long examples, and consider whether spaces, punctuation, or prefixes change the starting position.
Replace part of a cell or the whole cell
Find and Replace can match text inside a cell as well as a complete cell value. For example, replacing CA with California without checking Match entire cell contents could also alter a longer entry that contains those letters. If the intention is to replace only cells whose entire value is CA, turn on the full-cell option and verify the matching scope.
Replacing an empty value takes care: leaving Replace with blank removes the matched text, which may be intentional for deleting a suffix or unwanted character. But removing a space, hyphen, or other delimiter can join words or parts of an identifier. For example, changing a hyphen to nothing turns AB-12 into AB12; that is not equivalent to replacing it with a space.
Be especially cautious with dates, numbers, and codes. A cell may display a formatted value that differs from its underlying stored value, and a code that looks numeric may be stored as text to preserve leading zeros. Check representative cells after replacement rather than relying only on how the original data looks on screen.
Use wildcards carefully
Find and Replace supports wildcard searches. An asterisk (*) represents any number of characters, and a question mark (?) represents one character. For example, searching for pre* can match text beginning with “pre” followed by any characters. That broad pattern may catch many different words, so use it only when the intended matches share a reliable pattern.
To search for a literal asterisk or question mark, put a tilde (~) before the character. For example, ~* searches for an actual asterisk rather than treating it as a wildcard. This distinction is useful when cleaning data that contains symbols used in product names or notes. Test wildcard searches with Find All or individual matches before choosing Replace All.
Review results and avoid common problems
Find and Replace edits cells directly, while a formula creates a result that can be checked before it replaces the source. For larger or important changes, use a working copy and compare the old and new values. A formula column is especially useful when you need an audit trail or want to spot unexpected results.
After using a formula, copy the results and use Paste Special > Values if you want fixed text rather than formulas. On Windows, right-click the destination and choose the Paste Values option, or use Home > Paste > Paste Values. The exact icons can vary by ribbon layout. Keep the original column until you have confirmed that the results are correct.
Common causes of a replacement that seems not to work include an incorrect sheet or workbook scope, an enabled case-sensitive option, a full-cell match setting, or a search set to look in a different kind of content. Hidden spaces can also make two strings that look alike behave differently. If a term has leading or trailing spaces, inspect the source data and test a formula on a sample row rather than broadening a replacement blindly.
Another risk is changing formulas. If the search is set to look in formulas, a search term that appears inside a formula can be replaced as part of the formula text. That can change what the formula calculates or make it invalid. For workbook-wide replacements, first check the search settings and consider testing on a duplicate file.
When Power Query is a better fit
Power Query is useful when the same cleanup needs to happen every time data is imported or refreshed. In Excel for Microsoft 365 on Windows, load or open the query in the Power Query Editor, select the relevant column, and choose Transform > Replace Values. Enter the value to find and its replacement, then confirm the transformation. Menu details can vary depending on the editor layout and how the query was opened.
Unlike a one-time Find and Replace, a Power Query transformation becomes part of the query steps and can be applied again when refreshed data arrives. It does not directly edit the source file; it transforms the data brought into the query. Review the preview and confirm that the replacement applies to the intended column and values before loading the results back to Excel.
Frequently Asked Questions
How do I replace text in Excel without changing the original?
Use a formula such as SUBSTITUTE in a separate column. It returns the revised text while leaving the source cell unchanged, so you can review the results before copying them over as values.
What is the difference between SUBSTITUTE and REPLACE in Excel?
SUBSTITUTE finds particular text and changes its matching occurrence or occurrences. REPLACE changes a set number of characters from a specified position, whether or not those characters form a known word.
Can Excel replace text in every worksheet at once?
Yes. In Find and Replace, set the search scope to Workbook if that option is available, then check the results carefully. This can affect many sheets, so saving a copy first is prudent.
Why does Excel Find and Replace miss a word?
Check whether the search is limited to one sheet, whether Match case or Match entire cell contents is enabled, and what the dialog is set to look in. Extra spaces or different punctuation can also prevent an apparent match.
Can I replace only the first occurrence in a cell?
With a formula, use SUBSTITUTE’s optional occurrence number. For example, with text in A2, =SUBSTITUTE(A2,"red","green",1) changes only the first occurrence of “red” and leaves later occurrences alone.
Can I undo Replace All?
Excel’s Undo command, Ctrl+Z on Windows, will generally reverse the most recent replacement if it is still the latest action. If other actions have followed or the workbook has been closed, recovery may not be straightforward. For substantial changes, make a copy before replacing.
Related
- Excel Not Letting Me Insert a Row? Fixes That Work
- Excel Not Letting You Merge Cells? Fixes That Work
- Excel Not Inserting Columns? Common Causes and Fixes
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel UPPER Function: Convert Text to UppercaseIn Excel, use the UPPER function to convert text to uppercase: with the text to change in cell…
- COUNTIF Excel Criteria Examples and How to Use ThemExcel’s COUNTIF function counts cells that meet one condition. With A2:A9 containing Paid, Pending, Paid, Overdue, paid, Pending,…
- Excel Formula for All Caps: UPPER, Examples, and TipsTo make text all caps in Excel, use the UPPER function: with the text to convert in A2,…
- Median Formula for Class 10: Examples and StepsThe median is the middle value after data has been arranged in order. For ungrouped data, use the…
- Excel Last Day of Month: Formula and ExamplesTo return the last day of the month for a date in Excel, use =EOMONTH(A2,0), where A2 contains…
- Excel TRIM Formula Examples for Cleaning Messy TextIn Excel for Microsoft 365, use =TRIM(A2) to remove leading and trailing ordinary spaces from the text in…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.