SUBSTITUTE

You can use the SUBSTITUTE function to replace or remove a character. You can also use this function for multiple pairs.

Syntax:

SUBSTITUTE ( single-text-expression { , single-text-expression , single-text-expression } )

Meaning:

SUBSTITUTE (item_to_convert, one-or-more-text-substitution-pairs)

Where each one-or-more-text substitution-pair is text_to_change , substitute_text

Returns:

A single text item

SUBSTITUTE returns the text string that results from replacing all instances of the first text_to_change with substitute_text in item_to_convert, then replaces all instances of the second text_to_change with the substitute_text in the result of the first substitution, and so forth.

Examples

• SUBSTITUTE ( "123*456*7" , "*" , "/" )

  Finds 123*456*7 and returns 123/456/7

• SUBSTITUTE ( "120-45-6789" , "-" , "" )

  Finds 120-45-6789 and returns 120456789

• =SUBSTITUTE ("ABBA" , "B", "A", "A", "B")

  This example illustrates multiple searches for the SUBSTITUTE function.

  The first search-and-replace finds all "B"s and returns "A"s: AAAA

  The next search-and-replace finds all "A"s and returns "B"s: BBBB

  The end result is a return of: BBBB

Related functions

• LEAVEALPHA
• LEAVEALPHANUM
• LEAVENUM
• LEAVEPRINT
• SQUEEZE