Text.ReplaceRange is a Power Query M function that removes a specified number of characters from a text value and inserts a new text value at the same position. The function returns the modified text value with the newText inserted.
Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365
Syntax
Text.ReplaceRange(
text as nullable text,
offset as number,
count as number,
newText as text,
) as nullable textDescription
Returns the result of removing a number of characters, count, from text value text beginning at position offset and then inserting the text value newText at the same position in text.
In this syntax, ‘text’ denotes the original text value you’re working with, ‘offset’ is the position where you wish to start the replacement, ‘count’ is the number of characters to remove, and ‘newText’ is the string you wish to insert in place.
Examples
To better understand Text.ReplaceRange, let’s get hands-on with a couple of examples.
Let’s say you have a text string “RickVanGroot”, but you want to replace “Van” with “De”. You can leverage Text.ReplaceRange to make this change:
Text.ReplaceRange( "RickVanGroot", 4, 3, "De" )
In this instance, we start at the fourth character and replace the following three characters (“Van”) with the new text string (“De”).
In the same vein, suppose you simply want to remove “Van” from “RickVanGroot”. You can use Text.ReplaceRange and provide an empty string as the new text:
Text.ReplaceRange( "RickVanGroot", 4, 3, "" )
Here, we still begin at the fourth character and eliminate the next three characters (“Van”), but instead of inserting new text, we replace it with nothing, effectively removing “Van” from the original string.
When Text.ReplaceRange runs into a null value, it always return null.
Text.ReplaceRange( null, 4, 3, "De" )
With the ability to make precise replacements and character removals, Text.ReplaceRange adds a layer of flexibility to your text editing toolkit in Power Query M.
Related articles
Learn more about Text.ReplaceRange in the following articles:
- Text Functions in Power Query M (150+ Examples)
Your guide to Text Functions in Power Query M. Learn from practical examples and master Power Query’s most useful Text functions. » Read more
Related functions
Other functions related to Text.ReplaceRange are:
