Text.BetweenDelimiters - Text Function

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

Extracting Text Between the First and Second Delimiter

To get the text values between the first and second hyphen in the string “01-ab-99-p”, use the following formula. This will return “ab”.

Text.BetweenDelimiters( "01-ab-99-p", "-", "-" ) // Returns "ab"

Extracting Text Between the Second and Third Hyphen

To get the text values between the second and third delimiter, you can use a Relative Position Type to specify the starting position. The following example instructs to skip the first hyphen from the start, starting the extraction at the second hyphen. This will return “99”.

Text.BetweenDelimiters( "01-ab-99-p", "-", "-", { 1, RelativePosition.FromStart } ) // Returns "99"

Extracting Text with Positioning from the Start

You can specify both the starting and ending positions relative to delimiters in more complex strings. For example, in the string “11-22-33-44-55-66”:

Start Position: Looking from the end of the string, skip 3 hyphens. 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 from the start.

The following formula will return “33-44-55”:

// 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
)

Extracting Text with Positioning from the End

You can also extract values starting from the end of the string. For example, in the string “11-22-33-44-55-66”:

Start Position: Looking from the end of the string, skip 3 hyphens. 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 starting from the end.

The following formula will return “22”:

// 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
)

Extracting a Named Value from a JSON-Like String

To extract the “Name” portion from the JSON-like string {"ID":"500445","Name":"Freddy Mercury"}, use the following formula:

// Returns "Freddy Mercury"
Text.BetweenDelimiters(
    "{ “ID”:“500445”,“Name”:“Freddy Mercury” }", 
    ":“", 
    "”", 
    { 0, RelativePosition.FromEnd }, 
    0 )

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

Other functions related to Text.BetweenDelimiters are:

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/text-betweendelimiters