Text.Middle
TextReturns a substring starting at the given position, optionally limited to a specified number of characters.
Syntax
Text.Middle(text as nullable text, start as number, optional count as nullable number) as nullable textParameters
| Name | Type | Required | Description |
|---|---|---|---|
text | text | Yes | The source text value. |
start | number | Yes | The zero-based index of the first character to return. |
count | number | No | The number of characters to return. If omitted or null, returns all characters from start to the end of the text. |
Return Value
text — The substring of text beginning at start for count characters, or to the end of the string if count is omitted.
Remarks
Text.Middle returns a substring from text beginning at the zero-based start index. If count is provided, at most count characters are returned. If count is omitted or null, all characters from start to the end of the string are returned.
If text is null, the function returns null. If start is beyond the end of the string, an empty string is returned — not an error. If count extends beyond the available characters, all remaining characters are returned without error. This lenient behavior differs from languages like Python where an out-of-bounds slice raises an exception.
Text.Middle and Text.Range are functionally equivalent — both accept a start position and optional character count. Text.Middle is the name generated by the Power Query UI's "Extract > Text Between Positions" option. Use whichever name is clearer in context; the functions are interchangeable.
For extracting from the very start of a string, Text.Start is more expressive. For extracting from the very end, use Text.End. For a single character by position, Text.At is cleaner than Text.Middle(text, i, 1).
Examples
Example 1: Extract from a position to the end of the string
Text.Middle("Sales Rep", 6)Result | |
|---|---|
| 1 | Rep |
Example 2: Extract a fixed-length segment from a product code
Text.Middle("PRD-2024-001", 4, 4)Result | |
|---|---|
| 1 | 2024 |
Example 3: Extract the department code from a structured employee title
Text.Middle("Sr. Engineer", 0, 3)Result | |
|---|---|
| 1 | Sr. |