Text.BetweenDelimiters is a Power Query M function that extracts the portion of text between specified start and end delimiters. The function returns the extracted text, with optional parameters to specify which occurrences of the start and end delimiters to consider and how to index relative to each other.
Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365
Syntax
Text.BetweenDelimiters(
text as nullable text,
startDelimiter as text,
endDelimiter as text,
optional startIndex as any,
optional endIndex as any,
) as any| Argument | Attribute | Description |
|---|---|---|
| Text | This is the base text value from which to extract text. | |
| startDelimiter | Indicates the starting delimiter to search for. | |
| endDelimiter | Indicates the ending delimiter to search for. | |
| startIndex | optional | Indicates which delimiter to consider as startindex. You can either: 1. provide an index number. This instructs the function to find that nth occurrence of the delimiter. 2. provide both an index number and a RelativePosition Type. This instructs whether indexing should be done from start or end of the input. You can choose from RelativePosition.FromStart of RelativePosition.FromEnd. Example argument: { 1, RelativePosition.FromStart } |
| endIndex | optional | The endIndex is similar to the startIndex, except that indexing is done relative to the startIndex. |
Description
Returns the portion of text between the specified startDelimiter and endDelimiter. An optional numeric startIndex indicates which occurrence of the startDelimiter should be considered. An optional list startIndex indicates which occurrence of the startDelimiter should be considered, as well as whether indexing should be done from the start or end of the input. The endIndex is similar, except that indexing is done relative to the startIndex.
Examples
Get the text values between the first and second hyphen.
= Text.BetweenDelimiters( "01-ab-99-p", "-", "-" ) // Returns "ab"Get the text values between the second and third hyphen. The Relative Position Type instructs to skip the first hyphen from the start.
= Text.BetweenDelimiters( "01-ab-99-p", "-", "-", { 1, RelativePosition.FromStart } ) // Returns "99"Extract text from the string “11-22-33-44-55-66” with the following instructions.
- Start position: looking from the end of the string, skip 3 hypens. The next hyphen you find is your starting position (so the 4th one from the end).
- End position: from your starting position, look for the third hyphen you find. This is the end of the string to extract.
// Returns "33-44-55"
= Text.BetweenDelimiters(
"11-22-33-44-55-66", // text value
"-", // start delimiter
"-", // end delimiter
{ 3, RelativePosition.FromEnd }, // startDelimiter position
{ 2, RelativePosition.FromStart } // endDelimiter position
)You can also extract values the other way around.
- Start position: looking from the end of the string, skip 3 hypens. The next hyphen you find is your starting position (so the 4th one from the end).
- End position: from your starting position, look for the first hyphen you find starting from the end.
// Returns "22"
= Text.BetweenDelimiters(
"11-22-33-44-55-66", // Text value
"-", // start delimiter
"-", // end delimiter
{ 3, RelativePosition.FromEnd }, // startDelimiter position
{ 0, RelativePosition.FromEnd } // endDelimiter position
)Extract the “Name” portion from this string: {“ID”:“565113”,“Name”:“Ed Sheeran”}
// Returns "Freddy Mercury"
= Text.BetweenDelimiters(
"{ “ID”:“500445”,“Name”:“Freddy Mercury” }",
":“",
"”",
{ 0, RelativePosition.FromEnd },
0 )Related articles
Learn more about Text.BetweenDelimiters in the following articles:
- Text Functions in Power Query M (150+ Examples)
Your guide to Text Functions in Power Query M. Learn from practical examples and master Power Query’s most useful Text functions. » Read more
Related functions
Other functions related to Text.BetweenDelimiters are:
