> For the complete documentation index, see [llms.txt](https://concentra-analytics.gitbook.io/working-with-data/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://concentra-analytics.gitbook.io/working-with-data/strings/replace.md).

# 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](https://developer.mozilla.org/en/docs/Web/JavaScript/Guide/Regular_Expressions) or test them out yourself at [regexr.com](http://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](https://orgvue.gitbooks.io/formula-translator/content/general/variables.html).