Exceljet

Quick, clean, and to the point

Excel SUBSTITUTE Function

Excel SUBSTITUTE function
Summary 

The Excel SUBSTITUTE function replaces text in a given string by matching. For example =SUBSTITUTE("952-455-7865","-","") returns "9524557865"; the dash is stripped. SUBSTITUTE is case-sensitive and does not support wildcards.

Purpose 
Replace text based on content
Return value 
The altered text.
Syntax 
=SUBSTITUTE (text, old_text, new_text, [instance])
Arguments 
  • text - The text to change.
  • old_text - The text to replace.
  • new_text - The text to replace with.
  • instance - [optional] The instance of old_text to replace with new_text. Optional; if not supplied, all instances of old_text are replaced with new_text.
Usage notes 

Use the SUBSTITUTE function when you want to replace text based on its content, not position.

  • SUBSTITUTE finds and replaces old_text with new_text in a text string. Instance limits SUBSTITUTE replacement to one particular instance of old_text. If instance is not supplied, all instances of old_text are replaced with new_text.
  • instance is optional. if not supplied, all instances of old_text are replaced with new_text.
  • Use SUBSTITUTE to replace text based on content. Use REPLACE when to replace text based on its location.
  • SUBSTITUTE is case-sensitive and does not support wildcards.

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.