Downloads
Power Query MTextIntermediate

Text.BetweenDelimiters

Returns the text that sits between a start delimiter and an end delimiter.

Syntax
Text.BetweenDelimiters(text as nullable text, startDelimiter as text, endDelimiter as text, optional startIndex as any, optional endIndex as any) as nullable text

Returns: The text between the two delimiters, or null when either delimiter is not found or the input is null.

What it does

Text.BetweenDelimiters pulls out a value that sits inside markers, such as an invoice number written in brackets in a bank memo or a reference wrapped in parentheses. You give the opening and closing markers, and the function returns what is between them.

Syntax and arguments

Example

A customer's bank memo carries the invoice being paid inside square brackets. The team pulls the invoice number out so the payment can be applied to it.

let
    Memos = #table(type table [Memo = text],
        {{"ACH credit Acme Supply [INV1043] thanks"}, {"Wire from Blue Harbor, no reference"}}),
    Parsed = Table.AddColumn(Memos, "Invoice",
        each Text.BetweenDelimiters([Memo], "[", "]"), type text)
in
    Parsed

The first row gives "INV1043". The second has no brackets, so the result is null, which is the signal to send that payment to a person for review rather than applying it by guess.

Example: a second, different use

Get the amount text inside parentheses from a memo such as "Adjustment (1,250.00) posted", then convert it to a number.

Things to watch

In business terms

Remittance memos and bank descriptions often carry an invoice or check number inside a marker. Pulling that number out automatically is what lets cash application match most payments without someone reading each memo.

Related functions

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