SUBSTITUTE Function (DAX)

How does the SUBSTITUTE function (DAX) work?

The SUBSTITUTE function (DAX) replaces existing text with new text in a text string.

SUBSTITUTE Formula Syntax

SUBSTITUTE (
     <text>, <old_text>, <new_text>, <instance_num>
)

How do you use the SUBSTITUTE function?

The SUBSTITUTE function can replace text by matching. Use the SUBSTITUTE function when you want to replace text based on its content, not position. Optionally, you can specify the instance of found text to replace (i.e. first instance, second instance, etc.).

Related Blog Posts

Related Support Forum Posts

Replacing Substitute from Excel
Today vs Yesterday
Sales from a specific date

Considerations when using the SUBSTITUTE function

  • SUBSTITUTE finds and replaces old_text with new_text in a text string.
  • Instance limits SUBSTITUTE replacement to one particular instance of old_text. if not supplied, all instances of old_text are replaced with new_text.
  • Use SUBSTITUTE to replace text based on content. Use the REPLACE function to replace text based on its location.
  • SUBSTITUTE is case-sensitive and does not support wildcards.

Related Video Tutorials

Formula examples using the SUBSTITUTE function

=SUBSTITUTE([Product Code], “NW”, “PA”)

=SUBSTITUTE(B6,”t”,”b”)

=SUBSTITUTE(B8,”cat”,”dog”)

Related DAX Functions

REPLACE

Related Course Modules

Ultimate-Beginners-Guide-To-PowerBI

DOWNLOAD

To access our comprehensive DAX formula reference guide just insert your email address below. The resource will download immediately and we’ll also email it to you.