Downloads
DAXTextIntermediate

SUBSTITUTE

Replaces text that matches a search string with new text.

Syntax
SUBSTITUTE(<text>, <old_text>, <new_text> [, <instance_num>])

Returns: Text (a scalar).

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

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.

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:

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

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.

Related functions

All DAX functions · View as Markdown · DAX fundamentals