Text.BetweenDelimiters
Returns the text that sits between a start delimiter and an end delimiter.
Text.BetweenDelimiters(text as nullable text, startDelimiter as text, endDelimiter as text, optional startIndex as any, optional endIndex as any) as nullable textReturns: 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
- text: the value to search.
- startDelimiter: the text that opens the piece you want.
- endDelimiter: the text that closes it.
- startIndex and endIndex (optional): choose which occurrences of the delimiters to use when there is more than one. Leave them out for the first.
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
- A missing delimiter gives null, not an error. Treat null as "needs review".
- Only the first matching pair is returned unless you use the index arguments.
- Delimiters are matched exactly and are case sensitive if they contain letters.
- Use this for markers that are clearly defined. For free text, a keyword search is safer.
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.