If you want to pull a value out of a longer string, like the order number tucked inside a pair of brackets, Text.BetweenDelimiters grabs the piece between two markers you pick.
It works in Excel (Power Query), Power BI Desktop, and Power BI Service.
Syntax of Text.BetweenDelimiters Function
Text.BetweenDelimiters(text as nullable text, startDelimiter as text, endDelimiter as text, optional startIndex as any, optional endIndex as any) as any
where
text(required, nullable text). The text value you want to extract a piece from.startDelimiter(required, text). The marker that comes right before the part you want.endDelimiter(required, text). The marker that comes right after the part you want.startIndex(optional, any). Which occurrence ofstartDelimiterto use. Pass a number for a 0-based occurrence (0is the first), or a list like{n,RelativePosition.FromStart}or{n,RelativePosition.FromEnd}to count from the start or the end.endIndex(optional, any). Which occurrence ofendDelimiterto use, counted relative to the chosen start position. Takes the same number-or-list form asstartIndex.
Returns: the text found between the two delimiters. If the start delimiter isn’t found, it returns empty text "".
So you tell it what comes before and what comes after, and it hands back whatever sits in between.
Example 1: Extract a value sitting inside brackets
Pull the order number out of a status message where it’s wrapped in square brackets.
Text.BetweenDelimiters("Order [SO-4471] confirmed", "[", "]")
Result: SO-4471
The function finds the [, then the ], and returns everything between them.
Example 2: Grab text between two identical delimiters
When the same character separates several values, the function returns the part between the first two of them.
Text.BetweenDelimiters("red|green|blue|yellow", "|", "|")
Result: green
It stops at the first | and ends at the second |, so you get green.
Example 3: Target a later occurrence with a numeric startIndex
Add a startIndex and endIndex to skip ahead to a different pair of delimiters.
Text.BetweenDelimiters("red|green|blue|yellow", "|", "|", 1, 0)
Result: blue
1 picks the second | as the start (counting from 0), and 0 takes the next | after it, giving blue.
Example 4: Count delimiters from the end of the string
Pass a list with RelativePosition.FromEnd to start counting from the right side instead of the left.
Text.BetweenDelimiters("a-b-c-d-e", "-", "-", {1, RelativePosition.FromEnd}, {0, RelativePosition.FromStart})
Result: d
{1,RelativePosition.FromEnd} lands on the second-to-last -, and {0,RelativePosition.FromStart} takes the next - after it, returning d.
Example 5: What happens when the start delimiter is missing
If the start delimiter isn’t anywhere in the text, the function doesn’t throw an error.
Text.BetweenDelimiters("plain text no brackets", "[", "]")
Result: "" (empty text)
There’s no [ in the string, so the function returns an empty text value ("") instead of failing.
Things to keep in mind with Text.BetweenDelimiters
- Delimiters match exactly, including case.
"[Tag]"searched with"tag"won’t match. The characters have to line up exactly. nulltext input returnsnull. Feeding a blank-prone column straight in givesnullrather than"", so guard it if you need a text result.- It runs locally. This is a text operation that almost never folds, so on Excel, CSV, or folder sources it processes in Power Query itself, which is fine for normal row counts.
Common questions about Text.BetweenDelimiters
How is it different from Text.Middle or Text.Range?
Text.BetweenDelimiters finds the piece based on markers in the text, so you don’t need to know its position. Text.Middle and Text.Range pull a slice by character position, which only works when the value always sits at the same spot.
How do I get just the text before or after a single delimiter?
Use Text.BeforeDelimiter for everything ahead of a marker, or Text.AfterDelimiter for everything after it. They take one delimiter instead of two. To just test whether a marker exists first, Text.Contains returns true or false.
List of All Power Query Functions
Related Power Query Functions / Articles:
