SUBSTITUTE Function – Explained with Examples

Excel’s SUBSTITUTE function can be used either to replace or remove a single character, or a group of characters, from text. This can be either a specified number of times, or for every occurrence from the beginning of the text.

Function Syntax

=SUBSTITUTE(text, old_text, new_text, [instance_num])
  1. The first argument is the text to be parsed (or processed),
  2. The old_text is the character or text we wish to change,
  3. The new_text is the character or text we wish to use instead,
  4. instance_num
    1. is the number of times the old_text is substituted in the text. or
    2. by omitting the instance_num argument completely all possible substitutions will be made.

Usage

Replacing specific characters

  • old_text can be printable text or non printable character,
    • Non-printing characters can be referenced using the CHAR function into the old_text argument.

Removing text

  • Where we merely wish to remove text we can pass in blank text to the new_text argument, using 2 empty double quotes “”

Examples

Replacing a single character

we pass in our text, but we will not limit the number of substitutions, so we omit the 4th argument instance_num

=SUBSTITUTE("The quick brown fox", "f" , "F")

our “f” is capitalised in the output

The quick brown Fox

Replacing multiple characters

=SUBSTITUTE("The quick brown fox", "brown" , "red")

Our fox has changed color

The quick red fox

Limiting the text replacement

=SUBSTITUTE("The brown brown fox", "brown" , "quick", 1)

Only the first “brown” has been substituted

The quick brown fox

Removing unwanted words or characters

So to remove explicitly every “s” character from text we would pass old_text as “s”, new_text as “”, and omit the instance number.

=SUBSTITUTE("she sell sea shells by the sea shore", "s", "")

All are s’s are gone!

he ell ea hell by the ea hore

NOTE: SUBSTITUTE is case sensitive

Replacing Special Characters

The non-break space character is often used in Word Processing as a space which is intended to prevent an automatic line break at its position. It is often rendered as the ° symbol in word processing packages.

Depending on the context, it may be more appropriate to replace the non-break space character by a space character when text parsing, rather than being removed entirely.

Our example here will assume the input text is in cell A1

We can either use new_text = ” ” as the replacement value for old_text = non-break space:

=SUBSTITUTE(A1, CHAR(160), " ")

or more explicitly refer to the space character in the Window-1252 ASCII/ANSI table:

=SUBSTITUTE(A1, CHAR(160), CHAR(32))

TIP: CHAR(32) is the space character, but we can just use ” ” (with a space between the double quotes) for simplicity

Related Topics

F010