In Excel, use SUBSTITUTE to find specified text and replace it, or use REPLACE to change characters at known positions in a string. Both return a modified result in a cell without changing the original value; the key choice is whether you know what text to find or where the characters are.
What SUBSTITUTE and REPLACE Do
These similarly named functions solve different text-editing problems. SUBSTITUTE searches for a particular sequence of characters, such as a code, word, or punctuation mark. It replaces matches with new text. REPLACE does not search for a particular word or sequence: you give it a starting character number and the number of characters to remove, and it puts new text in that position.
For example, if a cell contains INV-2024-INV, you can tell SUBSTITUTE to find INV and replace it with ORD. If a cell contains AB-1234 and you want to replace the four characters beginning at position four, REPLACE is appropriate. The first function is based on content; the second is based on position.
Neither function edits the source cell in place. Enter the function in another cell, and Excel displays its result there. This makes the original data available for comparison and lets the result update when the source changes. To permanently replace worksheet contents, use Excel’s Find and Replace command instead.
Syntax and How the Functions Work
SUBSTITUTE has the syntax SUBSTITUTE(text, old_text, new_text, [instance_num]). The first three arguments are required. The optional instance_num tells Excel to replace only a particular occurrence. If you leave it out, every matching occurrence is replaced. Excel counts occurrences from left to right.
REPLACE has the syntax REPLACE(old_text, start_num, num_chars, new_text). start_num is the position of the first character to change, counting from 1. num_chars is how many characters to remove at that position. The function then inserts new_text. A value of zero for num_chars inserts text without removing any characters.
- Use
SUBSTITUTEwhen the old text is identifiable by its characters, even if its position varies. - Use
REPLACEwhen the target always occupies known character positions, even if the characters there vary. - Use either function in a formula to create a result while leaving the source cell unchanged.
Both functions are available in Microsoft 365 for Windows, Excel for Mac, and Excel for the web, and their basic syntax is the same in those versions. They are not new dynamic-array functions, so they also appear in many older Excel releases.
SUBSTITUTE Examples: Replace Text You Can Identify
Suppose cell A2 contains INV-2024-INV. With that text in A2, use the following formula to replace every occurrence of INV with ORD:
=SUBSTITUTE(A2,"INV","ORD")
The result is ORD-2024-ORD. Because the optional fourth argument is omitted, both matches change. This is useful for standardizing repeated prefixes or correcting a recurring spelling in a column of imported text.
With the same value in A2, use this formula to replace only the second occurrence:
=SUBSTITUTE(A2,"INV","ORD",2)
The result is INV-2024-ORD. If the requested occurrence does not exist, Excel leaves the text unchanged rather than returning an error. The occurrence number refers to matches of the complete old_text string, not individual characters inside it.
SUBSTITUTE is case-sensitive. If A2 contains Inv-2024-INV, searching for INV will change the uppercase match but not the mixed-case one. That matters when codes or names may have inconsistent capitalization. If the aim is to make text consistently uppercase or lowercase, consider using UPPER or LOWER as a separate step; those functions change case rather than search for and replace one particular phrase.
REPLACE Examples: Change Text by Position
Suppose A3 contains AB-1234. The characters are numbered A=1, B=2, hyphen=3, and the first digit=4. With that value in A3, this formula replaces four characters starting at position four:
=REPLACE(A3,4,4,"5678")
The result is AB-5678. This approach works well for fixed-format identifiers when the portion to update always begins at the same position. It is less suitable if extra spaces or variable-length prefixes can shift the target.
To insert a separator without deleting anything, suppose A4 contains 5551234567. With that number stored as text in A4, insert a hyphen before the fourth character by using:
=REPLACE(A4,4,0,"-")
The result is 555-1234567. The zero tells Excel not to remove any existing characters. This can be handy for inserting punctuation into consistently structured text, but it is not a reliable way to format actual numeric values such as phone numbers: Excel may remove leading zeros or display numbers according to cell formatting. For identifiers and phone numbers, storing the original data as text is often important.
Unlike SUBSTITUTE, REPLACE does not care whether the characters being changed match a particular word. It will change the positions you specify. It is also not case-sensitive in a search sense, because it performs no text search at all.
How to Choose the Right Function
Start by asking whether you can describe the target as a specific string or as a character position. If you need to replace every Ltd in a list, choose SUBSTITUTE. If you need to overwrite characters five through eight in fixed-length product codes, choose REPLACE. Choosing based on the actual structure of the data is more dependable than choosing based only on the desired-looking result.
- Choose SUBSTITUTE for known words, codes, spaces, separators, or other exact strings that may occur at different positions.
- Choose REPLACE for fixed-width fields where the section to change always has a known starting point and length.
- Use Find and Replace if you intend to change the cells themselves rather than create formula results in a separate range.
- Check the data first if text has inconsistent capitalization, spacing, or lengths; those differences can affect the output.
A common misconception is that REPLACE is simply a more powerful version of SUBSTITUTE. It is not. REPLACE cannot locate a phrase wherever it appears, and SUBSTITUTE cannot target a character position without identifying the text at that position. For mixed or irregular data, cleaning the values or using a more structured method may be necessary.
Replace Worksheet Values with Find and Replace
If you want to change existing cell contents rather than calculate new text, use Find and Replace. In Excel for Microsoft 365 on Windows, select the cells you want to affect, then choose Home > Find & Select > Replace, or press Ctrl+H. Enter the text to find and its replacement, review the options, and choose Replace for individual changes or Replace All for all matching results in the applicable search area.
On a Mac, open Excel’s Edit menu and use its Find/Replace command; the exact menu wording can vary slightly by Excel for Mac release. In Excel for the web, use the Find option on the Home tab and open Replace if available in the workbook interface. If a selection is active, check that the search is limited to the intended cells; otherwise, a replacement may affect more of the sheet than expected.
Find and Replace is a direct edit, not a formula. It can be useful for a one-time correction, but it does not preserve a live link to the previous value. Take care when searching formulas as well as displayed values: replacing a fragment found in a formula can alter the calculation itself. For larger changes, work on a copy or confirm the search scope before using Replace All.
Common Problems and Useful Checks
If a formula returns the original text, the old text may not match exactly. Check for leading or trailing spaces, different punctuation, or capitalization. SUBSTITUTE matches the specified characters literally; it does not treat * or ? as wildcard characters. A wildcard-style search in the Find and Replace dialog is a different feature from the SUBSTITUTE function.
If REPLACE changes the wrong characters, count positions from the first character, including spaces and punctuation. A hidden space at the beginning of a value shifts every later position. If text lengths vary, a fixed position may not point to the same meaningful part in every row. In that case, search-based replacement or a more deliberate parsing method is usually safer.
Both functions return text. If the output consists of digits but must be used in arithmetic, Excel may treat it as text rather than a number. Check the result’s alignment and behavior in calculations. You can convert suitable numeric text with VALUE, but conversion will not preserve leading zeros, which may be essential in account numbers, postal codes, or IDs.
For a formula result that does not update after editing the source, check whether calculation is set to automatic under Formulas > Calculation Options > Automatic. Also verify that the formula references the intended source cell. In a long list, fill the formula down and inspect a few results, especially when the inputs are not all the same length or format.
Combining Functions and Alternatives
You can nest text functions when one edit depends on another. For example, suppose A5 contains North / North / West, and you want to remove every space and then change each slash to a hyphen. With that text in A5, use:
=SUBSTITUTE(SUBSTITUTE(A5," ",""),"/","-")
The result is North-North-West. The inner function removes spaces first; the outer function replaces slashes in that intermediate result. Nested formulas can be useful for a small, repeatable cleanup, but they become hard to audit if many edits are stacked together. In that situation, separate helper columns can make each step easier to inspect.
For a one-off visual correction, Find and Replace may be simpler than a formula. For structured imports that need repeatable cleanup, Power Query can apply transformation steps during data preparation. For splitting or extracting text rather than replacing it, Excel has other text functions, including TEXTBEFORE and TEXTAFTER in Microsoft 365 and Excel 2024. Choose the method that matches whether you need a live formula, a permanent edit, or a reusable data-cleaning process.
Frequently Asked Questions
Can SUBSTITUTE replace only the first match?
Yes. Set the optional instance_num argument to 1 to replace the first matching occurrence. Leave that argument out to replace all occurrences, or use another occurrence number to target a later match.
Can REPLACE find a word anywhere in a cell?
No. REPLACE uses a starting character position and a character count; it does not search for a word. Use SUBSTITUTE when the text to find is known but its position may vary.
Are SUBSTITUTE and REPLACE case-sensitive?SUBSTITUTE is case-sensitive, so uppercase and lowercase text may be treated as different matches. REPLACE does not search for matching text, so case does not determine which characters it changes.
Do these functions change the original cell?
No. They return a result in the cell containing the formula and leave the source value intact. Use Find and Replace to edit existing contents directly, or copy formula results and paste them as values if you need a fixed result.
Why does SUBSTITUTE not replace a similar-looking value?
The specified old text must match the characters in the cell, including capitalization and spaces. Look for extra spaces, punctuation differences, or different letter case. The function does not use wildcard characters to match variations.
Related
- Project Timeline in Microsoft Excel: Build and Manage It
- Project Timeline Excel Template: Simple Planning Guide
- Sorting by Dates in Excel: A Clear Step-by-Step Guide
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- ROUND vs ROUNDUP in Excel: When to Use EachUse ROUND when you want a number rounded to the nearest place, and ROUNDUP when you need to…
- Excel Formula for the Tab Name: Get the Worksheet NameYes. In Excel for Microsoft 365 on Windows, enter =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255) in a cell on the worksheet whose name…
- Audit Formulas in Excel: Find Errors and Trace CellsTo audit formulas in Excel for Microsoft 365 on Windows, select a formula cell and choose Formulas >…
- Excel Quadratic Formula: Solve Equations in a SheetYes. Excel can calculate the roots of a quadratic equation with the quadratic formula: enter the coefficients a,…
- XLOOKUP in Excel: Find a Value and Return Matching DataTo find a value in Excel and return its matching result, use XLOOKUP: with product IDs in A2:A6,…
- Excel Time Sheets for Employees: Setup, Formulas and TipsYes. Excel can track employee start times, finish times, unpaid breaks, and hours worked in a reusable time…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.