Text.RemoveRange - Text Function

Text.RemoveRange is a Power Query M function that removes a specified number of characters from a text value starting at a specified position. The function returns the modified text value with the characters removed, with an optional count parameter to customize the removal.

Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365

Syntax

Text.RemoveRange(
   text as nullable text,
   offset as number,
   optional count as nullable number,
) as nullable text

Description

The Text.RemoveRange function removes specified portions of a text value based on their position and length. This function allows precise elimination of characters from any position within a string. The default value of count is 1. Position values start at 0.

For example, it can remove a single character, multiple characters from the start, or a specific range of characters within a text string.

Examples

Let’s further explore the Text.RemoveRange function in Power Query M language using a series of examples. Text.RemoveRange serves as a useful tool for eliminating specified portions of text values based on their position and length.

Removing a Single Character

Let’s start by removing a single character from the text value “ABEFC” at position 2. If we don’t tell Text.RemoveRange how many characters to remove (the optional third argument), it will just remove one. The position indexing in Power Query M language begins at 0, so position 2 refers to the third character in the string:

Text.RemoveRange( "ABEFC", 2 ) // Returns "ABFC"

Removing Multiple Characters from the Start

In some cases, you might need to remove multiple characters from the start of a string. For instance, let’s strip the first five characters from the text value “9-AR-C-85”:

Text.RemoveRange( "9-AR-C-85", 0, 5 ) // Returns "C-85"

Removing Characters from a Specific Position

The Text.RemoveRange function allows for precise character removal from any specified position in a string. Suppose we want to start from position 3 (fourth character) and remove the next three characters in the string “9-AR-C-85”:

Text.RemoveRange( "9-AR-C-85", 3, 3 ) // Returns "9-A-85"

Removing a Specific Length of Characters

You can also remove specific character sets within a string, not just single characters. By specifying the length of characters to be removed from a particular position, you can be very precise about what values to remove. Consider removing two characters from “ABEFC” starting at position 2:

Text.RemoveRange( "ABEFC", 2, 2 ) // Returns "ABC"

Learn more about Text.RemoveRange 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.RemoveRange are:

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