REPLACE - Replacing a Substring in a String

  • Last update: May 30, 2025
  • Usage One: Replacing a Specified Substring

    Overview

    Syntax

    REPLACE(Full text, Old text, New text)

     Replaces the content specified by the Old text in the Full text with the New text.

    Parameter 1

    Full text

    The text or referenced cell containing the substring to be replaced

    Parameter 2

    Old text

    The specified substring to be replaced

    Parameter 3

    New text

    The text used to replace the old text

    Return Value Type

    Text

    Notes

    • If the full text is NULL, the result is NULL.

    • If the full text is not NULL, but the old text is NULL, the function returns the original full text directly.

    • If the new text is NULL, the function removes the substring matching the old text from the full text and returns the result.

    Example

    For example, you need to replace Group in the Customer Name field with Joint-Stock Company, as shown in the following figure.

    1.3-1.png

    Add a New Calculation Column operator in the Data Transformation node and input the formula REPLACE(Customer Name, "Group", "Joint-Stock Company "), as shown in the following figure.

     More examples:

    FormulaResult

    REPLACE("abcd","a","re")

    rebcd

    REPLACE("a**d","**d","rose")

    arose

    Usage Two: Replacing a Substring at a Specified Position

    Overview

    Syntax

    REPLACE(Full text, Starting position, Character count, New text)

    Replaces part of the Full text with the New text, based on the specified Character count and Starting position.

    Parameter 1

    Full text

    The text or referenced cell containing the substring to be replaced

    Parameter 2

    Starting position

    The starting point where the old text is replaced with the new text

    Parameter 3

    Character count

    The number of characters in old text that need to be replaced with the new text

    Parameter 4

    New text

    The text used to replace the old text

    Notes

    • If the full text is NULL, the result is NULL.

    • If the full text is not NULL, but the old text is NULL, the function returns the original full text directly.

    • If the new text is NULL, the function removes the substring matching the old text from the full text and returns the result.

    Example

    For example, the function can be used to mask phone numbers by replacing certain digits with *****, as shown in the following figure.

    2.3-1.png

    FormulaResult

    REPLACE("0123456789",5,4,"*")

    0123*89

    REPLACE("1980",3,2,"99")

    1999

    附件列表


    主题: Data Development - Scheduled Task
    Previous
    Next
    • Helpful
    • Not helpful
    • Only read

    滑鼠選中內容,快速回饋問題

    滑鼠選中存在疑惑的內容,即可快速回饋問題,我們將會跟進處理。

    不再提示

    10s後關閉

    Get
    Help
    Online Support
    Professional technical support is provided to quickly help you solve problems.
    Online support is available from 9:00-12:00 and 13:30-17:30 on weekdays.
    Page Feedback
    You can provide suggestions and feedback for the current web page.
    Pre-Sales Consultation
    Business Consultation
    Business: international@fanruan.com
    Support: support@fanruan.com
    Page Feedback
    *Problem Type
    Cannot be empty
    Problem Description
    0/1000
    Cannot be empty

    Submitted successfully

    Network busy