Text.Middle
Returns characters from the middle of a text value, starting at a zero-based position.
Text.Middle(text as nullable text, start as number, optional count as nullable number) as nullable textReturns: The characters from start for count positions, or to the end when count is left out, or null when the input is null.
What it does
A code often carries a date or a sequence number in a fixed position in the middle of the text. Text.Middle pulls out a run of characters from a given position. The first character is position 0, which is the usual M rule and the one most people get wrong at first.
Syntax and arguments
- text: the value to read from.
- start: the zero-based position of the first character to return. The first character is 0.
- count (optional): how many characters to return. Leave it out to return everything from start to the end.
Example
A bank export puts the posting date inside a reference such as "PMT20261029-0042". The eight digits after the "PMT" prefix are the date, and they start at position 3.
let
Reference = "PMT20261029-0042",
DateText = Text.Middle(Reference, 3, 8),
Year = Text.Start(DateText, 4),
Month = Text.Middle(DateText, 4, 2),
Day = Text.End(DateText, 2)
in
{DateText, Year, Month, Day}
DateText is "20261029", Year is "2026", Month is "10" and Day is "29". Once the parts are text, Date.FromText or #date with Number.FromText can turn them into a real date.
Example: a second, different use
Take the sequence number from a voucher reference with everything after position 9 left out, so a report can group vouchers by batch.
Things to watch
- Position 0 is the first character. Counting from 1, as Excel does, is the most common mistake.
- If count runs past the end of the text, you get the characters that exist and no error.
- The result is text. Convert with Number.FromText or Date.FromText before doing arithmetic.
- Null in gives null out.
In business terms
Reference numbers from banks, payment processors and ERP systems often pack a date, a batch number or a store code into fixed positions. Pulling those parts out is how a team matches a remittance to a batch or builds a period column without retyping anything.