With the text to edit in cell A2, use =SUBSTITUTE(A2,"-","") to remove every hyphen; if A2 contains INV-204-INV, the result is INV204INV. Excel’s SUBSTITUTE function replaces specified text with other text, either everywhere it occurs or at one chosen occurrence.
What the SUBSTITUTE function does
SUBSTITUTE is a text function for changing part of a cell’s contents. It looks for an exact piece of text, called the old text, and returns a revised text value with that piece replaced by the new text. It is useful for cleaning imported data, standardizing labels, removing punctuation, and making consistent codes or names.
The function returns its result in the cell containing the formula. It does not edit the original source cell. That makes it suitable when you want to preserve raw data in one column and create a cleaned version in another. For example, put a formula in B2 to transform A2, then fill the formula down for the rest of the rows.
SUBSTITUTE works on text. If a cell contains a number, Excel can convert it to text as part of the operation, so the result may no longer behave like a numeric value in later calculations. If you need a numeric result, convert the finished text back to a number where appropriate, and check that the replacement has not changed the value’s meaning.
SUBSTITUTE syntax and arguments
The function has four arguments: the original text, the text to find, the replacement text, and an optional occurrence number. The first three are required. The occurrence number is optional and determines whether Excel replaces every match or only one particular match.
- Text: The text string or cell containing the content you want to change.
- Old text: The exact character, word, or substring Excel should find.
- New text: What Excel should put in its place. Use an empty text string, written as
"", to remove the old text. - Instance number: The match to replace, counting from left to right. Leave it out to replace every matching occurrence.
Text values typed directly into a formula need straight double quotation marks. Cell references do not need quotation marks. Excel formulas in this article use commas as argument separators, which is the standard in many English-language installations; regional settings may require semicolons instead.
Replace every occurrence or just one
If you leave out the instance number, SUBSTITUTE replaces all exact matches in the text. This is the usual choice for removing a repeated delimiter or changing every appearance of a word. If you include an instance number, Excel changes only that occurrence, counting from the left.
With the text North, West, North in A2, this formula changes only the second appearance of “North”; its result is North, West, South:
=SUBSTITUTE(A2,"North","South",2)
The instance number must be a positive integer. If the requested occurrence does not exist, the original text is returned unchanged. For example, choosing occurrence 3 when a string contains only two matches does not create an error; there is simply no third match to replace.
This option is helpful when a string contains repeated separators or repeated words but only one position should change. It is not a way to identify a match by its surrounding meaning: SUBSTITUTE counts matching occurrences in order, not by a rule such as “replace the match after a particular word.”
Practical examples for cleaning text
Here are common tasks where SUBSTITUTE is more convenient than editing values by hand:
- Remove dashes, slashes, or other unwanted characters from product IDs.
- Change a standard abbreviation or label throughout a cell.
- Replace a particular occurrence in a string that repeats the same text.
- Clean several kinds of punctuation by nesting SUBSTITUTE functions.
With a phone number stored as text in A2, the following formula removes all hyphens. For an A2 value of 555-010-2020, the result is 5550102020:
=SUBSTITUTE(A2,"-","")
To remove two different characters, nest one SUBSTITUTE inside another. With AB-12/3 in A2, this formula removes both the hyphen and slash, returning AB123:
=SUBSTITUTE(SUBSTITUTE(A2,"-",""),"/","")
In a nested formula, Excel evaluates the inner function first. Here, it removes hyphens, then the outer function removes slashes from that intermediate result. Add another nested function for each additional character you need to remove, but keep the formula readable; for many different cleanup rules, a helper column or a more suitable data-cleaning method may be easier to maintain.
Case sensitivity and exact matching
SUBSTITUTE is case-sensitive. It treats blue, Blue, and BLUE as different text, so a formula searching for lowercase “blue” will not change the other capitalizations. This is useful when case matters, but it can explain why a replacement appears to miss some cells.
With Blue blue BLUE in A2, the formula below changes only the lowercase match and returns Blue green BLUE:
=SUBSTITUTE(A2,"blue","green")
SUBSTITUTE looks for the exact characters supplied in the old-text argument. It does not treat asterisks or question marks as wildcards. If you need to replace text without regard to capitalization, first decide how to standardize case, or use a different approach suited to the data. Converting a whole string to lowercase or uppercase can itself change content that needs to retain its original capitalization.
How to enter the formula and fill it down
In Excel for Microsoft 365 on Windows, select the cell where you want the result, type an equals sign followed by SUBSTITUTE, and enter the arguments in parentheses. For example, if A2 contains the source value, enter a formula that refers to A2 and press Enter. The result appears in the selected cell, while A2 remains unchanged.
- Keep the original values in one column, such as A2:A100, and reserve the adjacent column for results.
- Enter the formula in the first result row, such as B2, using A2 as the text argument.
- Press Enter and check the result against the original value.
- Use the fill handle at the lower-right corner of B2 to copy the formula down the column. Excel adjusts a relative reference such as A2 to A3, A4, and so on.
If every row should use the same source cell or replacement text stored in a fixed cell, use an absolute reference for that part, such as $D$1. A dollar sign locks the row and column when you copy the formula. You can also double-click the fill handle to fill down alongside a neighboring contiguous data column.
The SUBSTITUTE function itself works in Excel for Microsoft 365 on Windows, Mac, and the web. The formula syntax is the same, although keyboard behavior and ribbon layout can vary slightly by platform and version. On Windows, Ctrl+C and Ctrl+V copy and paste; on Mac, the usual equivalents are Command+C and Command+V.
SUBSTITUTE versus REPLACE and Find and Replace
SUBSTITUTE and REPLACE have similar names but solve different problems. SUBSTITUTE searches for matching text; REPLACE changes characters based on their position and length. Use SUBSTITUTE when you know which exact text should change, and use REPLACE when you know the character position to edit.
With ACCT-5812 in A2, the formula below replaces four characters starting at position 6 with four zeroes. The returned text is ACCT-0000:
=REPLACE(A2,6,4,"0000")
For a one-time manual change in a worksheet, Find and Replace may be faster than creating a formula. In Excel for Windows, open Home > Find & Select > Replace, or press Ctrl+H. Review the search and replacement text, and choose whether to search the current sheet or the workbook before using Replace or Replace All. A formula is preferable when you want a repeatable result, need to preserve the source values, or expect the source data to change and want the output to update automatically.
Be careful with Replace All: it edits matching cell contents rather than producing a separate output column. Consider making a copy of the data or workbook before a broad replacement, especially when the search text could occur in places you did not intend.
Common problems and ways to check results
If a formula returns the original text, Excel may not have found an exact match. Check for different capitalization, extra spaces, punctuation that looks similar, or characters copied from a web page. Some data contains nonbreaking spaces or other invisible characters that ordinary space or punctuation searches will not match.
If Excel displays the formula itself instead of its result, check whether the cell is formatted as Text. Change the cell format to General, then re-enter the formula. Also check whether Show Formulas is enabled; on Windows, Ctrl+` toggles formula display in many Excel configurations.
If a formula produces a result that looks right but later calculations fail, the result may be text rather than a number. That is expected for many text substitutions. Test the cleaned value in a calculation before relying on it, and use a deliberate conversion step if a numeric value is required. Also confirm that removing punctuation does not merge separate identifiers or strip meaningful leading zeroes.
For systematic checks, compare a few results to the source rows and include examples with no match, one match, and several matches. If the formula is copied down, verify the first and last rows, and make sure any fixed references have dollar signs where needed. A replacement that is correct for one sample may be wrong for another if the data is inconsistent.
Frequently Asked Questions
Does SUBSTITUTE change the original cell?
No. It returns revised text in the formula cell and leaves the source value unchanged. To replace the original values, you would need a separate editing step, such as copying the results and pasting values over the source after checking them.
How do I remove text with SUBSTITUTE?
Use an empty replacement string as the third argument. For example, use "" as the new text to remove the matching character or substring. If you leave out the instance number, all matches are removed.
Can SUBSTITUTE replace only the first match?
Yes. Set the optional instance number to 1 to change the first matching occurrence. Set it to 2 for the second, and so on. Without that argument, Excel replaces every match.
Why does SUBSTITUTE ignore some matches?
It may be a capitalization mismatch because SUBSTITUTE is case-sensitive, or the source may contain a different character, extra spacing, or an invisible character. Check the exact text in the cell and test the formula on a copied sample.
Should I use SUBSTITUTE or REPLACE?
Choose SUBSTITUTE to find and change a particular string of characters. Choose REPLACE when the change should start at a specific character position and cover a specified number of characters. For a one-off manual change, Find and Replace can be more direct.
Choosing a reliable approach
For repeatable cleanup, place a SUBSTITUTE formula in a separate results column, test it on representative data, and fill it down only after checking the output. Use the optional occurrence number when only one repeated match should change. If capitalization, invisible characters, or inconsistent source formats complicate the replacement, investigate those differences before applying a broad transformation. That small validation step helps preserve identifiers and makes the finished worksheet easier to audit.
Related
- Subscript in Excel: Shortcut and Formatting Steps
- Quick Forecast in Excel: Steps, Limits, and Practical Tips
- Excel Redo Shortcut: The Quick Key and When It Works
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- IFERROR Leave Blank in Excel: Formula and ExamplesTo leave a cell blank when a formula returns an error in Excel, wrap the formula in IFERROR…
- Excel List Unique Entries with Formulas and Built-In ToolsTo list unique entries in Microsoft Excel for Microsoft 365, enter =UNIQUE(A2:A100) in an empty cell, replacing the…
- Excel Comparison Formulas: Functions and ExamplesExcel comparison formulas use operators such as =, <>, >, and <= to test values, usually returning TRUE…
- Excel Filter Not Equal to Text? How to Exclude ValuesTo filter out a particular text value in Excel for Microsoft 365 on Windows, select a cell in…
- How to Find Repeated Names in Excel with COUNTIF FormulaTo find repeated names in Excel, put names in A2:A100, then enter =COUNTIF($A$2:$A$100,A2) in B2 and fill it…
- Excel Sort A to Z: How Numbers Are SortedIn Excel, Sort A to Z sorts numeric values in ascending order—from the smallest number to the largest.…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.