replace(
replace(
TEXT
,
OLD
,
NEW
,
...
)

The replace( function replaces text with new text.

Parameters

This function has three required parameters:

text – is the item of text that you want to search through and possibly replace part of.

old – is the character, word or phrase that you want to search for and replace. This must be the exact character, word, or phrase, including upper or lower case. The replace( function will not match Dr with DR or dr.

new – is the new character, word or phrase that you want to substitute for the old character word or phrase.

… – The function may have additional pairs of old and new text, you can add as many pairs as you need.

Description

The replace( function partially replaces text with new text. It searches through an item of text looking for a character, word or phrase. If the function finds an exact match (including upper/lower case) the function replaces the old character, word or phrase with a new character, word or phrase.

    replace("His favorite colors are red and blue","red","vermillion")
        ☞ His favorite colors are vermillion and blue
    replace("His favorite colors are red and blue","His","Her")
        ☞ Her favorite colors are red and blue

Remember, if the match is not exact, it won’t take place:

    replace("His favorite colors are red and blue","his","her")
        ☞ His favorite colors are red and blue

You have to be careful to avoid unintended replacements.

    replace("This is a test","his","her") ☞ Ther is a test
    replace("Fred won a prize.","red","purple") ☞ Fpurple won a prize.

The replace( function must have at least three parameters, but it may also have 5, 7, 9 or more parameters. If there are additional parameters, they must appear in pairs of old and new values. When there are multiple pairs, each pair replacement is performed from left to right.

    replace("552-9017",
        0,"zero ",
        1,"one ",
        2,"two ",
        3,"three ",
        4,"four ",
        5,"five ",
        6,"six ",
        7,"seven ",
        8,"eight ",
        9,"nine ",
        "-","- ") ☞ five five two - nine zero one seven
    replace("Modesto, CA",
        "AZ","Arizona",
        "CA","California",
        "NV","Nevada",
        "OR","Oregon",
        "WA","Washington") ☞ Modesto, California

Error Messages

replace( function is missing a replacement value. – The replace( function must have at least three parameters, but it may also have 3, 5, 7, 9 or more parameters. If there are additional parameters, they must appear in pairs: old and new values. This error message appears if the last pair is missing a new value (in other words if there are 4, 6, 8, 10, etc. parameters.

See Also

- @regex --
- search( --
- batchreplace -- performs multiple find and replace operations on a piece of text.
- batchreplace( -- performs multiple find/replace operations on a piece of text.
- croptext( -- crops text to a specified length. If the text was already shorter than the specified length, it is left unchanged. If it is longer, the text is cut off and an ellipsis is added on the end.
- percentescape( -- encodes specified characters using percent encoding.
- percentunescape( -- decodes text that contains percent encoding.
- regexreplacefirst( -- replaces the first occurrence of a regular expression pattern with new text.
- regexreplacefirstexact( -- replaces the first occurrence of a regular expression pattern with new text.
- replacefirst( -- replaces the first occurence of some text with new text.
- replacemultiple( -- replaces multiple words and phrases in a text value.
- sandwich( -- assembles a text item from three smaller items.
- strip( -- strips off leading and trailing blanks and other whitespace (carriage returns, tabs, etc.)
- stripchar( -- extracts characters you don't want from a text item.
- stripdiacriticals( -- strips diacritical marks from text.
- stripprintable( -- strips non-printable characters from text.
- striptoalpha( -- removes everything but alphabetic letters from a text item.
- striptonum( -- removes everything but numeric digits from a text item.
- urldecode( -- decodes text that contains percent encoded characters.
- urlencode( -- converts text into a legal URL (by converting spaces into `%20`). WARNING: THIS FUNCTION IS OBSOLETE.

History

Version | Status | Notes
10.0 | Updated | This function now allows multiple pairs of old/new values. When there are multiple pairs, each pair replacement is performed from left to right.