Text.Range - Text Function

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

The Text.Range function 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

Let’s look at a few examples of how the Text.Range function works.

Extracting a Single Character

Suppose you have the text “Run@” and you want to extract the character at position 3. By using 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 "@"

Extracting a Character with a Specified Count

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, to extract the character at position 3 from the text “Run@”, you can specify the count as 1.

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

Handling Out-of-Range Positions

When attempting to extract a character at a position that does not exist within the text, an error occurs. For example, trying to extract the character at position 5 from the text “Run@” will result in an error indicating that the offset is out of range.

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

Extracting Substrings with a Start Position and Count

You can use Text.Range to extract substrings with a specified start position and count. 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:

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.Range are:

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