Text.Middle

Updated on

Text.Middle is a Power Query M function that extracts a specified number of characters from a text value starting at a specified position. The function returns the extracted substring with the specified length.

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

Syntax

Text.Middle(
   text as nullable text,
   start as number,
   optional count as nullable number,
) as nullable text

Description

Returns count characters, or through the end of text; at the offset start. It allows you to specify the position from which the extraction should commence and optionally determine the number of characters to extract. Unlike the Text.Range function, the Text.Middle function returns a value even when out of range.

Examples

Let’s delve into some examples to illustrate the usage of the Text.Middle function:

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

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

The Text.Middle 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.Middle with the text “Run@”, a start position of 3, and a count of 1. This will yield “@” as the extracted character.

Text.Middle ( "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.Middle will return an error indicating that the offset is out of range.

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

Text.Middle can be used to extract a range of characters as well.

For example, if you want to extract the substring from the text “Hello World” starting at index 6 until the end of the text, you can use Text.Middle with the text “Hello World”, a start position of 6, and a count value that is larger than the length of the remaining characters. This will result in the extracted substring “World.”

Text.Middle( "Hello World", 6, 20 ) // Returns "World"

Learn more about Text.Middle in the following articles:

Other functions related to Text.Middle are:

BI Gorilla Youtube Channel

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