Excel Tip of the Week #88 - SUBSTITUTE

Hello and welcome back to the Excel Tip of the Week.  This week, we have a General User post in which we look at an incredibly useful text-editing function that is a formula equivalent of the classic “Find & Replace” feature: SUBSTITUTE.

What SUBSTITUTE does: SUBSTITUTE takes a text string and replaces a given bit with another – so for example, replacing all full stops with slashes, or replacing all 1s with 2s.  You can either replace all the instances of the term, or only the 1st/2nd/3rd/etc.  SUBSTITUTE can also be used to simply remove a certain string from something, and in combination with other functions can even tell you e.g. the number of words in a cell.

How to write a SUBSTITUTE formula:
     =SUBSTITUTE(original text,find,replace,instance number)
Original text – A reference to the original text that will be worked with.
Find – Called old_text in Excel; this is the text that will be found.  Can be either a single character or an entire string.  You can either type it in a cell and refer to that cell, or type directly in the formula with quote marks – e.g. “/” for “find forward slashes”.
Replace – Called new_text in Excel; this is the new text that will be inserted.  The set-up and rules are the same as for the Find term.  To simply remove the Find term, enter “” (empty quotes signifying null text) here.
Instance number – Optional input.  If this is left blank, SUBSTITUTE will remove all instances of the Find term.  If a number n is input here, it will only replace the nth instance of the Find term.  If the number input exceeds the number of instances, no change will be made.

Uses of this function:
 - Remove “junk” from cells output from e.g. accounting systems, e.g. unwanted spaces or other “filler” characters
 - Reformat dates typed with .s into correctly-formatted ones typed with /s
 - Replace placeholder values like ~ with specific information, e.g. when creating a form letter from a template
 - Combine with the LEN function to compute e.g. the number of spaces in a cell, which can be used as a word count.

Check out the attached file for examples of each of these applications, as well as some practice tasks.

Previous post 
TOTW index
Next post

This blog is brought to you by the Excel Community where you can find additional blogs, extended articles and webinar recordings on a variety of Excel related topics. In addition to live training events, Excel Community members have access to a full suite of online training modules from Excel with Business. There is also an online forum where you can ask questions and share ideas with other community members.