The Substitute function in PowerApps is used to replace occurrences of a specified substring within a text string with another substring. This can be particularly useful for data cleaning, text formatting, and dynamic text manipulation.
PowerApps – Substitute() function Syntax
Substitute(Text, OldText, NewText[, InstanceNumber])
- Text: The text string in which you want to make the replacements.
- OldText: The substring you want to replace.
- NewText: The substring you want to replace OldText with.
- InstanceNumber: (Optional) The instance of OldText to replace. If omitted, all instances of OldText will be replaced.
Notes:
- Case Sensitivity: The Substitute function is case-sensitive. "Apple" and "apple" will be treated as different strings.
- Instance Number: If the specified instance number is greater than the number of occurrences of OldText, no replacement will occur for that instance number.
PowerApps – replace all occurrences of string with other string
To replace all occurrences of "apple" with "orange" in the text "I like apple pie and apple juice":
Substitute("I like apple pie and apple juice", "apple", "orange")
This returns "I like orange pie and orange juice".
PowerApps – replace only specific string with other string
To replace only the first occurrence of "apple" with "orange":
Substitute("I like apple pie and apple juice", "apple", "orange", 1)
This returns "I like orange pie and apple juice".
PowerApps – replace variable string with other string
Suppose you have a variable MyText containing "Hello world, world is beautiful" and you want to replace the second occurrence of "world" with "Earth":
Set(MyText, "Hello world, world is beautiful");
Substitute(MyText, "world", "Earth", 2)
This returns "Hello world, Earth is beautiful".