SUBSTITUTE function

SUBSTITUTE returns a new text string where specified characters or substrings are replaced, optionally limiting the change to a particular occurrence.

=SUBSTITUTE(text, old_text, new_text, [instance_num])

Generate a SUBSTITUTE formula

Describe what you need. The generator will reach for SUBSTITUTE where SUBSTITUTE is the right tool, and tell you when it is not.

How to get a better answer
  • Name your columns by letter and by header: "column F (Net Value)" beats "the amount column".
  • State every condition, including the negatives — "not cancelled" changes the formula's shape.
  • Say where the data starts if it is not row 1, and whether it will grow.
  • Check the settings above match your spreadsheet: the wrong argument separator is a syntax error on your machine.

Arguments

How SUBSTITUTE reads its arguments
textrequiredold_textrequirednew_textrequiredinstance_numoptionalSUBSTITUTE →
ArgumentRequiredDescription
textRequiredThe original text (or cell reference) to be examined; if omitted or a non-text value, Excel coerces it to text.
old_textRequiredThe substring to find within text; if not found, the original text is returned unchanged.
new_textRequiredThe replacement string; can be empty to delete old_text occurrences.
instance_numOptionalOptional positive integer indicating which occurrence of old_text to replace; non-numeric or ≤0 triggers a #VALUE! error.

Returns

Returns a single text string with the requested replacements applied.

Availability

Excel: All · Google Sheets: Supported

Worked examples

1. Expand month abbreviation to full name

CategoryMonthBudgetedActualVariance
GroceriesJan500450-50
=SUBSTITUTE(B2, "Jan", "January")

Result: January

Cell B2 contains the abbreviation "Jan". SUBSTITUTE looks for that exact substring and replaces it with "January", returning the longer month name. Because only one instance exists, the optional instance_num argument is not needed.

2. Rename the header "Budgeted" to "Planned"

CategoryMonthBudgetedActualVariance
UtilitiesJan15016010
=SUBSTITUTE(C1, "Budgeted", "Planned")

Result: Planned

The header in C1 reads "Budgeted". SUBSTITUTE swaps that exact word with "Planned", producing a new header label while leaving the rest of the sheet untouched. This is useful for quickly re-branding column titles without editing each cell manually.

3. Capitalize only the second "e" in "Entertainment"

CategoryMonthBudgetedActualVariance
EntertainmentJan20025050
=SUBSTITUTE(A4, "e", "E", 2)

Result: EntErtainment

A4 contains the word "Entertainment". By specifying instance_num = 2, SUBSTITUTE replaces only the second occurrence of the lowercase "e" (the one after the "t") with an uppercase "E". The first "e" (the leading capital "E") and the third "e" remain unchanged, demonstrating fine-grained control over text replacement.

Common errors

Which SUBSTITUTE error are you seeing?
SUBSTITUTE returned an error#VALUE!
Provide a positive integer or omit the argument entirely so Excel defaults to replacing all occurrences.
#REF!
Restore the missing reference or adjust the formula to point to a valid cell containing the desired string.
#NAME?
Correct the spelling to SUBSTITUTE and ensure any named ranges referenced are defined.
ErrorWhy it happensHow to fix it
#VALUE!instance_num is supplied as text (e.g., "two") or as a negative number.Provide a positive integer or omit the argument entirely so Excel defaults to replacing all occurrences.
#REF!The cell reference supplied for text, old_text, or new_text points to a deleted or invalid range.Restore the missing reference or adjust the formula to point to a valid cell containing the desired string.
#NAME?The function name is misspelled (e.g., =SUBSTIUTE(...)) or a named range used inside the formula does not exist.Correct the spelling to SUBSTITUTE and ensure any named ranges referenced are defined.

Tips and when to use something else

  • Use SUBSTITUTE when you need to replace literal text; it does not understand patterns or wildcards.
  • Combine SUBSTITUTE with FIND or SEARCH if you must locate a position before replacement.
  • If you need to replace characters based on position rather than content, REPLACE is the better choice.
  • When working with arrays of text, wrap SUBSTITUTE in BYROW or BYCOL to apply it row-wise or column-wise.

Related functions

REPLACEREPLACE returns a new text string where a specified number of characters in the original text are substituted with new characters.FINDFIND returns the numeric position of a substring within a text string, counting from a specified start point and respecting case sensitivity.TEXTJOINTEXTJOIN merges multiple text strings or ranges into one string, using a chosen delimiter and optional ignoring of empty cells.LEFTLEFT returns the leftmost characters from a text string, optionally limited by a specified number of characters.RIGHTRIGHT returns the specified number of characters from the end of a text string, defaulting to one character if omitted.

Frequently asked questions

Can SUBSTITUTE replace case-insensitive matches?
No. SUBSTITUTE is case-sensitive, so "Jan" and "jan" are treated as different strings. To perform a case-insensitive replace, combine LOWER or UPPER with SUBSTITUTE, or use REGEXREPLACE in Google Sheets.
What happens if old_text is an empty string?
When old_text is empty, SUBSTITUTE returns the original text unchanged because there is nothing to match. To insert characters between every character, you would need a more complex formula involving MID or a custom LAMBDA.
How does SUBSTITUTE behave with numbers formatted as text?
Numbers are coerced to text before the replacement occurs. For example, SUBSTITUTE(12345, "3", "X") yields "12X45". If you need the result as a numeric value again, wrap the formula with VALUE.
Is SUBSTITUTE available in older versions of Excel?
Yes. SUBSTITUTE has been part of Excel since the early 2000s and works in all modern desktop, web, and mobile versions, as well as in Google Sheets.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator