<!-- https://learnsql.cpetoday.com/functions/dax/substitute -->
# SUBSTITUTE

Language: DAX
Category: Text
Syntax: SUBSTITUTE(<text>, <old_text>, <new_text> [, <instance_num>])
Summary: Replaces text that matches a search string with new text.
Returns: Text (a scalar).
Level: Intermediate
Related: REPLACE, TRIM, VALUE, SEARCH, FIND, LEFT

## What it does
SUBSTITUTE finds a piece of text and replaces it with something else. Every match is changed unless you name one instance. Use it to strip a prefix from a code, remove thousands separators before a conversion to number, or swap a standard word for another. To replace text by position rather than by content, use REPLACE.

## Syntax and arguments
- text: the value to change.
- old_text: the text to look for. The match is case sensitive.
- new_text: the text to put in place of each match. Use "" to remove the match.
- instance_num (optional): replace only the nth match. Without it, every match is changed.

## Example
Bank exports write amounts with a thousands separator, such as "12,450.00", which cannot be converted to a number directly. The separators must go first.

```dax
Amount Number =
VALUE(
    SUBSTITUTE(BankLines[AmountText], ",", "")
)
```

The comma is removed and the remaining "12450.00" is converted to a number. Test the result on a sample, because a locale with a different decimal symbol changes what VALUE expects.

## Example: a second, different use
Strip a customer reference prefix so that the number matches the customer master:

```dax
Customer Number = SUBSTITUTE(Invoices[CustomerRef], "CUST-", "")
```

A reference of "CUST-00421" becomes "00421". The result is still text, so keep the leading zeros.

## Things to watch
- The match is case sensitive. "cust-" is not replaced when the pattern is "CUST-".
- Every match is replaced by default. Use instance_num to change only one.
- An empty old_text does nothing useful. Always pass a real search string.
- SUBSTITUTE returns text. Convert to a number with VALUE only when the result is a number.
- Nested SUBSTITUTE calls are readable for two or three clean-up steps. Beyond that, clean the data at the source.

## In business terms
SUBSTITUTE cleans imported data before it is compared or added: thousands separators in amounts, prefixes in reference numbers, unwanted characters in tax IDs. It is a routine step in any bank or marketplace import that feeds a reconciliation.
