SUBSTITUTE Generator
Replace specific text inside a cell with something else, by content rather than position.
SUBSTITUTE
Replace specific text inside a cell with something else, by content rather than position.
The cell containing the text you want to change.
The exact text to look for, in quotes. Matching is case sensitive.
The replacement text, in quotes. Use "" to delete the found text instead.
Optional fourth argument: which occurrence to replace, counting from the left. Delete this argument and the comma before it to replace every occurrence.
=SUBSTITUTE(A2, "St.", "Street", 1)Worked Example
A2 contains the code 2026-Q1-North and you only want the second hyphen turned into a space.
=SUBSTITUTE(A2, "-", " ", 2)Returns: Returns 2026-Q1 North. The first hyphen is untouched because instance_num is 2.
Checks Before You Paste
- •SUBSTITUTE is case sensitive: "st" will not match "St". If case varies in your data, substitute on UPPER(A2) or LOWER(A2), or clean the source values first.
- •Leave instance_num out entirely to replace every occurrence. Supplying 1 changes only the first one, and a value of 0 or a negative number returns #VALUE!.
- •Nest it to strip several characters at once, for example =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2, "(", ""), ")", ""), "-", "") to clean up a phone number.
How SUBSTITUTE works
Availability: SUBSTITUTE is available in every version of Excel from 2007 onward, in Excel for Mac and for the web, and in Google Sheets with the same four arguments and the same case sensitivity. Google Sheets additionally offers REGEXREPLACE for pattern-based work; Excel's equivalent, REGEXREPLACE, is limited to Microsoft 365 with the newer function set.
SUBSTITUTE finds a piece of text by its content and swaps it for something else. You tell it what to look for and what to put in its place, and it works through the string from the left. Unlike Find and Replace on the ribbon, it does not touch the source cell - it returns a new value in the formula cell, which means the original data stays intact and the substitution recalculates whenever the source changes.
The distinction that matters is SUBSTITUTE against REPLACE. SUBSTITUTE works by content: replace "St." with "Street" wherever it appears, however many times, whatever position it is in. REPLACE works by position: replace three characters starting at character seven, whatever those characters happen to be. Content is what you want when the target moves around; position is what you want when the layout is fixed, such as masking the middle of an account number.
The optional fourth argument, instance_num, narrows the substitution to a single occurrence counted from the left. Leave it out and every occurrence is replaced. Supply 2 and only the second one changes. This is what makes SUBSTITUTE useful for parsing as well as cleaning: marking the nth delimiter with a character that cannot appear in the data gives you a position that FIND can locate, which is how the classic get-everything-after-the-last-separator formulas work.
Two behaviours are worth committing to memory before you build anything on it. First, matching is case sensitive: "st" will never match "St". Second, if old_text does not appear at all, SUBSTITUTE returns the original text unchanged rather than an error - convenient, but it also means a substitution that silently does nothing looks exactly like one that had nothing to do.
Syntax
=SUBSTITUTE(text, old_text, new_text, [instance_num])| Argument | Required | What it does |
|---|---|---|
text | Required | The text to work on: a cell reference, literal text in quotes, or another formula's text result. Numbers and dates are converted to text first, so a date is substituted as its serial number. |
old_text | Required | The exact text to find, in double quotes or as a reference. Matching is case sensitive. If this string does not occur in text, the original text comes back unchanged with no error. |
new_text | Required | What to put in its place. Use "" to delete the found text rather than replace it. It can be longer or shorter than old_text - unlike REPLACE, nothing about the surrounding positions has to line up. |
instance_num | Optional | Which occurrence to replace, counting from the left. Omit it and every occurrence is replaced - that is the default, not 1. A value larger than the number of occurrences leaves the text unchanged; 0, a negative number or text returns #VALUE!. |
More worked examples
A2 holds a phone number as it was typed by a user: (020) 7946-0018, and the CRM import needs digits only.
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2, "(", ""), ")", ""), "-", ""), " ", "")Returns: 02079460018
Four nested calls, each removing one character class. The result is text, which is what you want here - VALUE would strip the leading zero.
A2 holds the tag list red;green;blue;red and you want to know how many tags are in it.
=LEN(A2) - LEN(SUBSTITUTE(A2, ";", "")) + 1Returns: 4
Removing every semicolon shortens the string by exactly the number of semicolons, so the difference in length is the delimiter count. Add one for the number of items between them. This breaks if the cell is empty, so guard it with IF(A2="", 0, ...).
A2 holds the company name Northwind Traders Ltd and the report should show it without the legal suffix.
=TRIM(SUBSTITUTE(A2, " Ltd", ""))Returns: Northwind Traders
Deleting "Ltd" alone would leave a trailing space, and deleting "Ltd" without the leading space would also mangle a company genuinely called Ltdex. TRIM cleans up whatever is left at the ends.
Common mistakes
- Case sensitivity means half the rows go unchanged
- =SUBSTITUTE(A2, "street", "St") leaves "Main Street" and "MAIN STREET" exactly as they were. There is no case-insensitive switch. Either normalise the text first - =SUBSTITUTE(PROPER(A2), "Street", "St") - or nest one call per capitalisation you actually see in the data.
- Substituting a fragment that appears inside other words
- Replacing "St" with "Street" in "Stockton St" produces "Streetockton Street". SUBSTITUTE has no concept of word boundaries. Include the surrounding characters in old_text (" St" with a leading space, or " St." with the full stop), or use instance_num to pin down the one you mean.
- Nested substitutions cascading into each other
- =SUBSTITUTE(SUBSTITUTE(A2, "a", "b"), "b", "c") turns the original a characters into c, because the second call sees the output of the first. When you are swapping values around rather than deleting them, route through a placeholder no one will type - CHAR(1), say - so the passes cannot collide.
- instance_num of 0 returns #VALUE!
- The occurrences are numbered from 1, so 0 is invalid and gives #VALUE!, as does a negative number or text. If the count comes from another formula, it may legitimately evaluate to 0 on some rows; wrap it as =IF(n=0, A2, SUBSTITUTE(A2, "-", " ", n)).
- The result is text even when it looks numeric
- =SUBSTITUTE(A2, ",", "") on "1,234" gives the text "1234", which left-aligns and is skipped by SUM. Wrap it in VALUE when the column has to add up, and keep in mind that VALUE will also drop any leading zeros the substitution preserved.
Frequently Asked Questions
SUBSTITUTE finds text by what it says: =SUBSTITUTE(A2, "Ave", "Avenue") changes every Ave regardless of where it sits. REPLACE finds text by where it sits: =REPLACE(A2, 7, 3, "***") overwrites three characters starting at position seven, whatever they are. Use SUBSTITUTE when the target moves between rows and REPLACE when the layout is fixed, such as masking the middle digits of a card number.
Supply 2 as the fourth argument: =SUBSTITUTE(A2, "-", " ", 2) changes the second hyphen and leaves the first and any later ones alone. Occurrences are counted from the left, and there is no way to count from the right directly - for that, count the total occurrences with the LEN trick and pass the result as instance_num.
Not on its own. The workaround is to substitute against a case-normalised copy - UPPER(A2) or LOWER(A2) - accepting that the surrounding text changes case too, or to nest one SUBSTITUTE per variant you actually have in the data. If your Excel has REGEXREPLACE, its case-insensitive mode does the job in a single call.
Measure the string, remove the character, and take the difference: =LEN(A2)-LEN(SUBSTITUTE(A2, ",", "")). For a multi-character string, divide by its length: =(LEN(A2)-LEN(SUBSTITUTE(A2, "ab", "")))/2. Both are case sensitive, so wrap A2 in UPPER on both sides if case should not matter.
Three usual causes, in order of frequency: the case does not match; what looks like a space is a non-breaking space, CHAR(160), so " St" never matches; or an instance_num higher than the number of occurrences was supplied, in which case Excel returns the original quietly rather than erroring. Check the first two with =EXACT(A2, UPPER(A2)) and =CODE(MID(A2, n, 1)).