Text.ReplaceRange - Text Function

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

The Text.ReplaceRange function replaces a specified range of characters in a text string with new text. You define the starting position (offset) and the number of characters to replace (count), and the string to insert at the specific position (newText).

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.

Replacing a Substring

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”).

Removing a Substring

In the same manner, 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.

Handling Null Values

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:

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

Other functions related to Text.ReplaceRange are:

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