Replace

If a string needs to be changed in a predictable way but it's not feasible to do this at the source, or it's quite long and complicated, Replace provides a nice alternative. It lets you specify a value to find within a string, and replace it with a new substring of your choice.

Excel

In Excel, REPLACE uses a start index and length to determine the text being replaced, rather than searching for a specific string: =~REPLACE!~(‹string›, ‹start_index›, ‹length›, ‹new_string›)

=REPLACE(F10,2,4,"Dev") // "BDMT001" -> "BDev01"

NB. If you don't know the ‹start_index›, you can use ~FIND!~(‹target_string›, ‹within_string›) to replace everything after a specified character, e.g.:

=REPLACE(F10,FIND("DMT",F10),4,"Dev")

Tableau

~REPLACE!~(‹within_string›, ‹target_string›, ‹new_string›)

REPLACE([Cost Code], "BDMT0", "BDev") // "BDMT001" -> "BDev01"

Alteryx

There are three options for matching in Alteryx.

Option 1: Basic replacement ~REPLACE!~(‹within_string›, ‹target_string›, ‹new_string›)

REPLACE([Cost Code], "BDMT0", "BDev") // "BDEV001" -> "BDev01"

NB. This approach is case sensitive. Option 2: Case sensitivity and Regex matching ~REGEX_REPLACE!~(‹within_string›, ‹target_string›, ‹new_string›«, case_sensitive»)

REGEX_REPLACE([Cost Code], "bdmt0", "BDev") // case insensitive
REGEX_REPLACE([Cost Code], "BDMT0", "BDev", 0) // case sensitive

NB. Omitting case_sensitive or setting it to 1 means that REGEX_REPLACE() will ignore case. Including a 0 will make it respect case. Option 3: Replacement with a single character ~REPLACECHAR!~(‹within_string›, ‹target_characters›, ‹new_character›)

REPLACE([Cost Code], "BDEV", "X") // "BDEV001" -> "XXXX001"

NB. This approach is case sensitive.

OrgVue

‹within_string›.value.~replace!~("‹target_string›", "‹new_string›")

node.costcode.value.replace('BDMT0','BDev') // "BDMT001" -> "BDev01"

NB.:

• This approach is case sensitive, but you can use Regex to define the ‹target_string›.

• For more information about the replace() syntax, see the summary of Regular Expressions (Regex) here or test them out yourself at regexr.com.

tSQL

~REPLACE!~(‹within_field›, ‹target_string› , ‹new_string›)

SELECT REPLACE(CostCode,"BDMT","BDev") AS NewCostCode
FROM ProductsData
-- "BDEV001" -> "BDev01"

Python

‹within_string›.~replace!~(‹target_string›, ‹new_string›«, max_replacements»)

costCode.replace("BDMT","BDev") # "BDEV001" -> "BDev01"

prodCode.replace("0", "N", 2) # "0-0091MC" -> "N-N091MC"

NB.:

• When «max_replacements» is omitted, all ‹target_strings› are replaced. Otherwise, only as many are replaced as specified by «max_replacements» (starting from the left).

• The examples above assume variables have been declared for costCode and prodCode. For more information, go to General > Variables.