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 textDescription
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, the function returns a blank. For instance, if you have the text “Run@” and you want to extract the character at position 5, which is beyond the text length, you get an empty string.
Text.Middle ( "Run@", 5, 1 ) // Returns an empty string: ""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"Related articles
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
Related functions
Other functions related to Text.Middle are:
