SUBSTITUTE()

Syntax. SUBSTITUTE(text,old_text,new_text,instance_num)

Definition. The SUBSTITUTE() function replaces characters or strings with new text.

Arguments

• text (required). The text or the reference to a cell containing text in which you want to substitute characters

• old_text (required). The string you want to replace

• new_text (required). The string you want to replace old_text with

• instance_num (optional). Specifies which occurrence of old_text you want to replace with new_text. If you specify instance_num, only that occurrence of old_text is replaced; otherwise, every instance of old_text is replaced.

Background. Use this function to replace a string of text with alternative text. The replacement can be for a single or for multiple instances.

You can use the SUBSTITUTE() function to replace a specific string within text. Use the REPLACE() function to replace a string at a certain position within text.

Important

The SUBSTITUTE() function is case-sensitive when it is searching for old_text.

Example. Suppose you want to replace German special characters; for example, ä with ae, ö with oe, and ü with ue. To do this, you nest the SUBSTITUTE() function three times:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE("Dönerverkäuferprüfung","ä","ae"),"ö","oe"),"ü","ue")

This results in Doenerverkaeuferpruefung. Here are a few more examples:

• =SUBSTITUTE("intercontinentalflight","flight","drive") results in intercontinentaldrive.

• =SUBSTITUTE("cell","l","t",2) results in celt.

• =SUBSTITUTE("vetter","tt","nt",1) results in venter.

• =SUBSTITUTE("canter","A","e")results in canter.

• =SUBSTITUTE("million","m","b") results in billion.

See Also

FIND(), REPLACE(), REPLACEB(), SEARCH(), TRIM()

Sample Files

Use the SUBSTITUTE worksheet in the Text_Data.xls or Text_Data.xlsx sample file. The sample files are found in the Chapter08 folder. For more information about the sample files, see the section titled Using the Sample Files.