Text.Range

Updated on

Text.Range is a Power Query M function that extracts a substring from a text value starting at a specified position, with an optional count parameter to specify how many characters to return. The function returns the extracted substring.

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

Syntax

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

Description

Returns the substring from the text text found at the offset offset. An optional parameter, count, can be included to specify how many characters to return. Throws an error if there aren’t enough characters.

Examples

Suppose you have the text “Run@” and you want to extract the character at position 3. By utilizing the Text.Range function with the text “Run@” and a start position of 3, you will obtain “@” as the extracted character.

Text.Range( "Run@", 3 )    // Returns "@"

The Text.Range function can also be used to extract a single character by providing both the start position and a count of 1. For instance, if you have the text “Run@” and you want to extract the character at position 3, you can use Text.Range with the text “Run@”, a start position of 3, and a count of 1. This will yield “@” as the extracted character.

Text.Range( "Run@", 3, 1 ) // Returns "@"

When attempting to extract a character at a position that does not exist within the text, an error occurs. For instance, if you have the text “Run@” and you want to extract the character at position 5, which is beyond the text length, Text.Range will return an error indicating that the offset is out of range.

Text.Range( "Run@", 5, 1 ) // Returns error: offset is out of range

You can also use Text.Range to extract substrings with a defined count and specified range. For example, consider the text ” – – – “. If you want to extract the substring starting from position 1 with a count of 3, Text.Range will return “- -” as the extracted substring.

Text.Range( " - - - ", 1 )    // Returns "- - - "
Text.Range( " - - - ", 1, 3 ) // Returns "- -"

Alternatively, you can provide a specific count to extract a substring of a defined length. For instance, if you have the text “I am Groot” and you want to extract the substring starting from position 2 with a count of 2, Text.Range will give you “am” as the extracted substring.

Text.Range( "I am Groot", 2 )    // Returns "am Groot"
Text.Range( "I am Groot", 2, 2 ) // Returns "am"

Learn more about Text.Range in the following articles:

Other functions related to Text.Range are:

BI Gorilla Youtube Channel

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