Text.BetweenDelimiters
TextReturns the text between a start delimiter and an end delimiter, with optional occurrence control.
Syntax
Text.BetweenDelimiters(text as nullable text, startDelimiter as text, endDelimiter as text, optional startIndex as any, optional endIndex as any) as nullable textParameters
| Name | Type | Required | Description |
|---|---|---|---|
text | text | Yes | The source text value. |
startDelimiter | text | Yes | The delimiter marking the beginning of the extracted portion. |
endDelimiter | text | Yes | The delimiter marking the end of the extracted portion. |
startIndex | any | No | A zero-based occurrence index for the start delimiter, or a list {index, RelativePosition.FromEnd}. Defaults to 0. |
endIndex | any | No | A zero-based occurrence index for the end delimiter, or a list {index, RelativePosition.FromEnd}. Defaults to 0. |
Return Value
text — The substring found between the specified start and end delimiters.
Remarks
Text.BetweenDelimiters extracts the portion of text that lies between a startDelimiter and an endDelimiter. The search for the end delimiter begins immediately after the start delimiter position, so the delimiters can be the same character without ambiguity — the function finds the first occurrence of endDelimiter after startDelimiter.
The optional startIndex and endIndex parameters control which occurrence of each delimiter to use:
- A non-negative integer n specifies the zero-based occurrence (0 = first, 1 = second, etc.)
- A two-element list {n, RelativePosition.FromEnd} counts occurrences from the end of the string
If either delimiter is not found, an empty string is returned — not an error. If text is null, the function returns null. The delimiter search is always case-sensitive; there is no comparer parameter.
This function is the natural choice for extracting content from structured formats with distinct open and close delimiters — such as parentheses, brackets, XML-like tags, or quoted fields. For extracting everything before or after a single delimiter, use Text.BeforeDelimiter or Text.AfterDelimiter.
Examples
Example 1: Extract content between parentheses
Text.BetweenDelimiters("Widget A (SKU-001) - In Stock", "(", ")")Result | |
|---|---|
| 1 | SKU-001 |
Example 2: Extract a year from a bracketed report name
Text.BetweenDelimiters("Sales_Report[2024]_Final", "[", "]")Result | |
|---|---|
| 1 | 2024 |
Example 3: Extract the second bracketed value using startIndex
Text.BetweenDelimiters("[East][Alice][Widget A]", "[", "]", 1)Result | |
|---|---|
| 1 | Alice |
Example 4: Extract content from an XML-like string
Text.BetweenDelimiters("<Product>Widget A</Product>", "<Product>", "</Product>")Result | |
|---|---|
| 1 | Widget A |