Microsoft Excel Training Home

How to use the SUBSTITUTE function in Excel

An interesting use of the substitute function to count the number of words in a sentence

You can use the substitute function in Microsoft Excel to replace the complete text or a specific part of a text. For example, you could replace a space with 'nothing' or replace the first instance or occurrence of 'r' in 'Quarter'.
Syntax Substitute(text,existing_text,new_text, instance_num)
Text refers to a cell address like A3 containing the text.
Existing_text is the text we wish to substitute.
New_text is the final substituted text.
Instance_num specifies whether we wish to replace the first 'r' in 'Quarter' or the last 'r' or both, if no instance_num is indicated.


Substitute function in Microsoft Excel