To replace matching text in Excel, use SUBSTITUTE; to replace characters at a known position, use REPLACE. With the original text in A2, for example, =SUBSTITUTE(A2,"old","new") changes every lowercase “old” in that cell to “new.” These formulas return the changed text in another cell, leaving the source untouched. The examples below are for Excel for Microsoft 365 on Windows.
What You’ll Need
- An Excel workbook with the text or values you want to change, preferably kept in a source column.
- An empty cell or column for the results. Keeping the formula results separate makes it easier to review changes before using them.
- The exact text to find and the replacement text, or the character position and number of characters to replace.
Both REPLACE and SUBSTITUTE are standard Excel functions available in Microsoft 365. The formulas use commas between arguments, as in the examples. Excel may expect semicolons instead if your computer’s regional settings use semicolons as the list separator; if Excel flags a correctly copied formula, try the separator your installation uses.
Step-by-Step
- Decide whether you are matching text or a position. Use
SUBSTITUTEwhen you know the exact sequence to find, such as a word, punctuation mark, or code. It looks for that sequence wherever it appears in the cell. UseREPLACEwhen you know which character number to start at and how many characters to overwrite. For example, replacing a word in a sentence is usually aSUBSTITUTEtask; changing a fixed-width code’s year digits is often aREPLACEtask. Choosing by purpose avoids counting character positions when you do not need to. - Set up a result column. Put the original text in one column and leave the adjacent column for the formula. For instance, you might have descriptions in A2:A100 and put a formula in B2. Do not enter the formula over the source text: a formula that refers to its own cell creates a circular reference, and overwriting source data makes it harder to check the change. Add a heading such as “Updated text” above the results so it is clear which values are calculated.
- Use SUBSTITUTE to change every occurrence of a specific string. With the original text “old label – old code” in A2 and the result formula going in B2, enter
=SUBSTITUTE(A2,"old","new"). The result is “new label – new code.” The function takes the source text, the exact text to find, and the replacement text, in that order. It changes every matching occurrence by default, not just the first one. If the target phrase or replacement varies by row, you can refer to cells instead of typing the text inside quotation marks. - Replace only a particular occurrence when needed. With “old label – old code” in A2 and the formula in B2, enter
=SUBSTITUTE(A2,"old","new",2). The final argument, 2, tells Excel to change only the second occurrence, so the result is “old label – new code.” Set that argument to 1 to change only the first occurrence. If the selected occurrence does not exist, Excel leaves the text unchanged; it does not report an error. Omit the final argument when every matching occurrence should change. - Use REPLACE to overwrite characters by position. Suppose A2 contains “INV-2025-0048,” and you want to change the four year characters, which begin at character 5. With that value in A2 and the formula in B2, enter
=REPLACE(A2,5,4,"2026"). The result is “INV-2026-0048.” The arguments are the source text, starting character number, number of characters to replace, and replacement text. Count spaces and punctuation as characters: in this code, the three letters and the hyphen make the year begin at position 5. - Remove text by replacing it with an empty string. An empty pair of quotation marks means “replace with nothing.” With “555-123-4567” in A2 and the result formula in B2, enter
=SUBSTITUTE(A2,"-",""). The result is “5551234567.” This is useful for removing a repeated separator or unwanted character. It removes every hyphen in this example; if you need to remove only one particular hyphen, useREPLACEwith its position and a character count of 1. - Check the result before applying it to a full list. Compare the result with the source, including the beginning and end of the text. Check a few different cases: one with the target string, one without it, and one with multiple occurrences. For positional replacement, verify that the start number points to the intended character in every row. The same position may not identify the same field if the codes have different lengths or prefixes.
- Fill the formula down the column. Select the cell containing the checked formula, then drag its fill handle down, or copy it and paste into the remaining result cells. Excel adjusts a relative reference such as A2 to A3 on the next row automatically. If you use a table, typing the formula in the first result-row cell usually fills the calculated column. Review the bottom of the filled range as well as the first few results, especially if blanks or inconsistent source values are present.
- Keep formulas or turn the reviewed results into fixed text. Leave formulas in place if the source values may change and you want the results to update. If you need a fixed copy, select the result cells, copy them, and use Home > Paste > Paste Values. This replaces the selected formulas with their displayed results. Keep a copy of the original data before doing this, because the pasted values will no longer update when the source text changes.
On Excel for Mac, the two functions and the formula logic are the same. Ribbon names and layout can vary slightly by release, but you can enter a formula directly in the result cell or the formula bar. Excel for the web also supports these functions; when copying a formula, check that the destination range is the one you intend to fill.
Common Mistakes
Choosing the wrong function: SUBSTITUTE searches for a matching string, while REPLACE counts from the start of the text. A formula can return a plausible result while changing the wrong characters if you select the wrong method. If a target word can move around within a sentence, matching it with SUBSTITUTE is generally safer than assuming a fixed position.
Forgetting that SUBSTITUTE is case-sensitive: The search text “cat” does not match “Cat” or “CAT.” A formula may therefore leave some cells unchanged even though the text looks similar. Check capitalization in the source and in the search argument. Also remember that spaces count: searching for “blue ”, with a trailing space, is different from searching for “blue”.
Counting REPLACE positions incorrectly: Character positions start at 1, not 0, and include spaces and punctuation. If every record does not have the same format, a fixed start position may overwrite different parts of different values. Test the formula against a representative sample before filling it down.
Expecting wildcards or partial matching: SUBSTITUTE replaces an exact text sequence; asterisks and question marks are not wildcard instructions in its find argument. If you enter a broader phrase than intended, it will not automatically match variations. Specify the exact string you mean to replace, or use a different approach suited to the data pattern.
Replacing a value in place with a formula: A formula such as one that reads A2 should go in a different cell, not A2 itself. Putting it in the cell it refers to can produce a circular reference. Keep source and result columns separate until you have checked the output.
Tips
Use cell references for changing search and replacement text. For example, keep the word to find in D1 and the replacement in E1. This makes it easier to revise the rule without editing every formula. If you use fixed cells for those instructions and fill the formula down, make the references absolute with dollar signs so they do not shift; test the copied formula to confirm the references remain aimed at D1 and E1.
Keep a small sample of original and expected results when changing a large list. This makes it easier to spot a mistaken position, an unexpected capitalization, or a replacement that affects more occurrences than intended. For identifiers, product codes, and dates stored as text, confirm that the output remains in the required format. A formula returns text for these examples, which can matter if another calculation expects a number or a real Excel date.
Use Find and Replace from Home > Find & Select > Replace if you want to change cell contents directly rather than create a formula result column. That command is different from the formula method: it edits matching cells, while a formula calculates an output and preserves the source. If you need to audit, reverse, or selectively approve the changes, the separate formula column is usually easier to review.
Troubleshooting
The formula appears as text instead of calculating. Check that the cell is not formatted as Text. Change its format to General, then select the cell, press F2, and press Enter to make Excel re-evaluate the entry. Also confirm that the formula starts with an equals sign and that there is no apostrophe before it.
The result is unchanged. Confirm that the source cell actually contains the exact string or occurrence you specified. With SUBSTITUTE, check capitalization and spaces, because the match is case-sensitive and exact. If you specified an occurrence number, make sure the source contains that many matches. With REPLACE, confirm the start number and character count; it does not search for the text you intend to change.
Excel reports a formula problem. Check the commas, quotation marks, and parentheses, and make sure text arguments are enclosed in straight double quotes. If your regional settings use semicolons as argument separators, replace the commas with semicolons in the formula entered in Excel. Do not add quotation marks around a cell reference such as A2; quote literal text such as “old”.
The output changes the wrong part of a code. Recount the characters in the source, including hyphens and spaces, and check more than one row. If the section moves between rows, a fixed-position REPLACE formula is not a good fit. Use an exact match with SUBSTITUTE when possible, or standardize the source format before using position-based replacement.
The formula does not update after you edit the source. Check Formulas > Calculation Options > Automatic. If calculation is set to Manual, Excel may not recalculate immediately; switch to Automatic or recalculate the workbook. Also ensure the formula references the intended source cell rather than a fixed value typed into the formula.
Frequently Asked Questions
What is the difference between REPLACE and SUBSTITUTE in Excel?REPLACE changes a stated number of characters starting at a stated position. SUBSTITUTE finds exact text and replaces matching occurrences. Choose based on whether you know the location or the text to find.
Can SUBSTITUTE change just the first match?
Yes. Its optional fourth argument specifies which occurrence to replace. Use 1 for the first match, 2 for the second, and so on. Leave the argument out to replace every occurrence.
Can I use a cell reference for the replacement text?
Yes. Put the text to find and the replacement in separate cells, then refer to those cells in the formula. This is convenient when the replacement rule may change. Use absolute references if you will fill the formula down and want those instruction cells to stay fixed.
Can a formula replace text in the original cell?
Not while also referring to that same cell as its source; that creates a circular reference. Calculate the result in a separate cell or column. After checking it, copy the result and use Paste Values if you want to replace the original contents with fixed text.
Why does SUBSTITUTE ignore a word with different capitalization?SUBSTITUTE is case-sensitive, so uppercase and lowercase versions are treated as different text. Check the exact capitalization in the data. If capitalization varies, you may need to normalize the source first or use another approach designed for case-insensitive matching.
What happens if REPLACE starts beyond the end of the text?
If the start position is after the existing text, Excel appends the replacement text at the end. If the position is too early or the character count is wrong, the result may still be valid but not what you intended, so verify the character count using a sample value.
Related
- How to Add Borders in Excel: Step-by-Step Instructions
- How to Put Color in an Excel Cell: A Practical Guide
- How to Put Borders in Excel Cells: Clear Steps and Tips
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Mac Excel Filter Shortcut: Turn Filters On and Use ThemIn Excel for Mac, press Command+Shift+F to turn AutoFilter on or off for a data range. Select a…
- LAMBDA in Excel: A Practical Guide to Custom FunctionsYes—Excel’s LAMBDA function lets you create reusable custom functions without writing VBA. For a quick test, with no…
- Excel Formula Between Two Numbers: ExamplesTo check whether a value in A2 is between a lower limit in B2 and an upper limit…
- Calculate Age in Excel Using Date of Birth with DATEDIFTo calculate age in Excel from a date of birth, put the birth date in A2 and enter…
- Excel Formula for Last Updated Date in a Specific CellFor a live current date in Excel, use =TODAY(); for the current date and time, use =NOW(). Neither…
- Excel Filter Multiple Criteria in the Same Column?To filter one Excel column by several values, open its filter dropdown and select each value you want…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.