SUBSTITUTE
A text with every instance of another text, or only the n-th, replaced.
Group: Text. Compatible with DAX.
Syntax
SUBSTITUTE ( <text>, <old_text>, <new_text> [, <instance_num>] )| Parameter | Type | Description |
|---|---|---|
text | Text | The text to change. |
old_text | Text | The text to replace, exactly as it is written there. |
new_text | Text | What comes in its place; empty text, or a blank, removes it. |
instance_num | Whole number optional | Which occurrence to replace, from 1. Left out, every one. |
Returns
Text. The text with the old text replaced.
Remarks
- Upper and lower case count: p is not found in Pils.
- Occurrences are replaced from left to right and do not overlap: aaaa with aa replaced by b is bb.
- With instance_num only that occurrence changes. One past the last leaves the text as it was; 0 or less is an error.
- When old_text is not there, or is empty, the text comes back unchanged. A blank text gives BLANK.
- To replace by position instead, use
REPLACE.
Examples
Replacing every occurrence
EVALUATE
ROW (
"Depot", SUBSTITUTE ( "Oslo plant", "plant", "depot" ),
"Dashes removed", SUBSTITUTE ( "a-b-c", "-", "" ),
"Not found", SUBSTITUTE ( "Pils", "p", "X" )
)| Depot | Dashes removed | Not found |
|---|---|---|
| Oslo depot | abc | Pils |
Only the second one
EVALUATE
ROW (
"Second dash", SUBSTITUTE ( "a-b-c", "-", "/", 2 ),
"Second Pils", SUBSTITUTE ( "Pils Pils", "Pils", "IPA", 2 )
)| Second dash | Second Pils |
|---|---|
| a-b/c | Pils IPA |