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

Feedback