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