Text.Middle - Text Function

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

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

Extracting a Single Character

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

Extracting a Single Character with a Count

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 "@"

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 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

Extracting a Range of Characters

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:

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

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