Downloads
Power Query MTextBeginner

Text.Middle

Returns characters from the middle of a text value, starting at a zero-based position.

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

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

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

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.

Related functions

All Power Query M functions · View as Markdown · M basics