Text.ReplaceRange

Updated on

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 text

Description

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" )
Text.ReplaceRange replace substring in Power Query M

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, "" )
Text.ReplaceRange remove text in Power Query M

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" )
Text.ReplaceRange and null values in Power Query M

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.

Learn more about Text.ReplaceRange in the following articles:

Other functions related to Text.ReplaceRange are:

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/text-replacerange