Text

SUBSTITUTE Function in Excel

Replaces occurrences of old text with new text in a string.

Syntax

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

Arguments

  • text (required): The original text
  • old_text (required): Text to replace
  • new_text (required): Replacement text
  • instance_num (optional): Which occurrence to replace (omit for all)

Examples

  • =SUBSTITUTE("Hello World", "World", "Excel") - Replace World with Excel - Result: Hello Excel
  • =SUBSTITUTE(A1, " ", "") - Remove all spaces - Result: Text without spaces
  • =SUBSTITUTE("a-b-c", "-", "/", 2) - Replace only 2nd hyphen - Result: a-b/c

Excel SUBSTITUTE function documentation

  • SUBSTITUTE replaces specific text within a string — by search text, not by character position.
  • Syntax: =SUBSTITUTE(text, old_text, new_text, [instance_num]). Case-sensitive.
  • Use when you know the substring to swap — hyphens, domain names, typos, currency symbols.
  • Related: [REPLACE](/functions/replace/) (position-based), [REGEXREPLACE](/functions/regexreplace/) (M365 patterns).

SUBSTITUTE syntax and instance_num

  • Omit instance_num to replace every occurrence of old_text.
  • instance_num = 2 replaces only the second occurrence.
  • If old_text not found, SUBSTITUTE returns original text unchanged (not an error).
  • Case-sensitive: "Excel" ≠ "excel" — pair with UPPER for case-insensitive patterns or use SEARCH workflow.

Step-by-step: remove hyphens from product codes

  • Step 1 — Codes like AB-CD-123 in column A.
  • Step 2 — =SUBSTITUTE(A2, "-", "") removes all hyphens.
  • Step 3 — Only first hyphen: =SUBSTITUTE(A2, "-", "", 1).
  • Step 4 — Compare LEN before/after to confirm expected replacements.
  • Step 5 — For position-based edits use [REPLACE](/functions/replace/) instead.

SUBSTITUTE vs REPLACE vs REGEXREPLACE

  • SUBSTITUTE — find literal old_text anywhere in string.
  • REPLACE — start_num + num_chars position edit.
  • REGEXREPLACE — pattern-based in Microsoft 365.
  • Guide: [REPLACE function](/functions/replace/) · [REGEXREPLACE](/functions/regexreplace/).

Worked examples to copy

  • Remove spaces: =SUBSTITUTE(A2, " ", "").
  • Swap domain: =SUBSTITUTE(A2, "old.com", "new.com").
  • Second dash only: =SUBSTITUTE(A2, "-", "_", 2).
  • NBSP cleanup: =SUBSTITUTE(A2, CHAR(160), " ") before TRIM.

People also ask

  • SUBSTITUTE vs REPLACE? — SUBSTITUTE searches text; REPLACE uses character position.
  • Replace only first occurrence? — Use instance_num = 1.
  • SUBSTITUTE case sensitive? — Yes. Match case exactly or normalize with UPPER.
  • SUBSTITUTE not working? — old_text not found exactly including spaces.

Common errors

  • Case-sensitive replacement
  • Returns original if old_text not found

Use cases

  • Data cleaning
  • Format conversion
  • Text transformation

Frequently asked questions

  • What is the difference between SUBSTITUTE and REPLACE? SUBSTITUTE replaces specific text wherever it appears. REPLACE replaces characters at a specific position. =SUBSTITUTE("abc","b","x")="axc". =REPLACE("abc",2,1,"x")="axc". SUBSTITUTE is text-based, REPLACE is position-based.
  • Is SUBSTITUTE case-sensitive? Yes, SUBSTITUTE is case-sensitive. =SUBSTITUTE("ABC","a","x") returns "ABC" unchanged. For case-insensitive, combine with UPPER/LOWER or use complex formulas.
  • How do I replace only the Nth occurrence? Use instance_num parameter: =SUBSTITUTE(A1, "x", "y", 2) replaces only the second 'x'. Omit to replace all occurrences.
  • How do I remove specific characters? Replace with empty string: =SUBSTITUTE(A1, "-", "") removes all hyphens. Chain for multiple: =SUBSTITUTE(SUBSTITUTE(A1,"-","")," ","") removes hyphens and spaces.
  • How do I count occurrences of a character? Use: =(LEN(A1)-LEN(SUBSTITUTE(A1,"x","")))/LEN("x"). This counts how many times 'x' appears by measuring length difference after removal.

Editorial review

  • Reviewed by Excel.Directory Editorial Team. Updated May 2026.

When to use SUBSTITUTE

  • Data cleaning — common Text scenario for SUBSTITUTE.
  • Format conversion — common Text scenario for SUBSTITUTE.
  • Text transformation — common Text scenario for SUBSTITUTE.

SUBSTITUTE in the Text category

  • Browse all Text functions at /categories/text/ for related formulas.
  • SUBSTITUTE syntax: =SUBSTITUTE(text, old_text, new_text, [instance_num])
  • text (required): The original text
  • old_text (required): Text to replace
  • new_text (required): Replacement text
  • instance_num (optional): Which occurrence to replace (omit for all)
  • Confirm SUBSTITUTE arguments match the syntax shown above before filling down.
  • Lock table and range references with $ when copying formulas across rows or sheets.

Formula checklist before you copy down

  • Confirm SUBSTITUTE arguments match the syntax shown above before filling down.
  • Lock table and range references with $ when copying formulas across rows or sheets.
  • If results look wrong, check for text stored as numbers and invisible spaces with TRIM.
  • Spot-check three known input rows manually against expected output.

Related Excel functions

  • REPLACE (/functions/replace/): Replaces part of a text string with a different text string, based on the number of characters you specify.
  • FIND (/functions/find/): Finds one text string within another and returns the starting position (case-sensitive).
  • SEARCH (/functions/search/): Finds one text string within another and returns the starting position (case-insensitive, supports wildcards).
  • TRIM (/functions/trim/): Removes extra spaces from text, leaving only single spaces between words.
  • CLEAN (/functions/clean/): Removes all non-printable characters from text.

Errors to watch for

  • Case-sensitive replacement — review causes on linked error pages in the directory.
  • Returns original if old_text not found — review causes on linked error pages in the directory.

Copy-paste audit workflow

  • Enter SUBSTITUTE on three test rows with known expected output documented on a QA tab.
  • Fill down only after absolute references are locked on lookup tables and rate tables.
  • Compare against manual calculation or a calculator for financial and statistical functions.
  • Search this directory for comparison guides when choosing between similar functions in the same category.

SUBSTITUTE worked examples to copy

  • =SUBSTITUTE("Hello World", "World", "Excel") — Replace World with Excel. Expected result: Hello Excel.
  • =SUBSTITUTE(A1, " ", "") — Remove all spaces. Expected result: Text without spaces.
  • =SUBSTITUTE("a-b-c", "-", "/", 2) — Replace only 2nd hyphen. Expected result: a-b/c.

SUBSTITUTE reference summary for crawlers and offline review

  • SUBSTITUTE belongs to the Text category in Excel. Replaces occurrences of old text with new text in a string.
  • Full syntax: =SUBSTITUTE(text, old_text, new_text, [instance_num]). Open /functions/substitute/ for parameters, FAQs, and related pages.
  • Common mistakes: Case-sensitive replacement; Returns original if old_text not found
  • Pair this function with comparison guides when another Excel formula might fit the same task better.
  • Review fix-excel-formula-errors when unexpected errors appear after upgrading Excel or sharing across locales.