Text.AfterDelimiter

Text.AfterDelimiter is a Power Query M function that extracts the portion of text after a specified delimiter. The function returns the extracted text, with optional parameters to specify which occurrence of the delimiter to consider and whether to index from the start or end of the input.

Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365

Syntax

Text.AfterDelimiter(
   text as nullable text,
   delimiter as text,
   optional index as any,
) as any

Argument	Attribute	Description
Text		This is the base text value from which to extract text.
Delimiter		Indicates the delimiter to search for.
Index	optional	Indicates which delimiter to consider. 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 }

Description

Returns the portion of text after the specified delimiter. An optional numeric index indicates which occurrence of the delimiter should be considered. An optional list index indicates which occurrence of the delimiter should be considered, as well as whether indexing should be done from the start or end of the input.

Examples

Get the portion of “11-22-33-44” after the ( first ) hyphen.

= Text.AfterDelimiter( "11-22-33-44", "-" )    // Returns "22-33-44"
= Text.AfterDelimiter( "11-22-33-44", "-", 0 ) // Returns "22-33-44"

Get the portion of “11-22-33-44” before the second hyphen from the start.

= Text.AfterDelimiter( "11-22-33-44", "-", 1 )                                 // Returns "33-44"
= Text.AfterDelimiter( "11-22-33-44", "-", { 1, RelativePosition.FromStart } ) // Returns "33-44"

You can also be more explicit by using a RelativePosition.Type.

// Return the part after the second hyphen from the start
// Returns:  "33-44"
= Text.AfterDelimiter( "11-22-33-44", "-", { 1, RelativePosition.FromStart } ) 

// Return the part after the second hyphen from the end
// Returns:  "33-44"
= Text.AfterDelimiter( "11-22-33-44", "-", { 1, RelativePosition.FromEnd } )

// Return the part before the first hyphen from the end
// Returns:  "44"
= Text.AfterDelimiter( "11-22-33-44", "-", { 0, RelativePosition.FromEnd } )

A quick glance for possible options then gives you:

= Text.AfterDelimiter( "11-22-33-44", "-" )                                    // Returns "22-33-44"
= Text.AfterDelimiter( "11-22-33-44", "-", { 0, RelativePosition.FromStart } ) // Returns "22-33-44"
= Text.AfterDelimiter( "11-22-33-44", "-", { 1, RelativePosition.FromStart } ) // Returns "33-44"
= Text.AfterDelimiter( "11-22-33-44", "-", { 2, RelativePosition.FromStart } ) // Returns "44"
= Text.AfterDelimiter( "11-22-33-44", "-", { 0, RelativePosition.FromEnd } )   // Returns "44"
= Text.AfterDelimiter( "11-22-33-44", "-", { 1, RelativePosition.FromEnd } )   // Returns "33-44"
= Text.AfterDelimiter( "11-22-33-44", "-", { 2, RelativePosition.FromEnd } )   // Returns "22-33-44"

Related articles

Learn more about Text.AfterDelimiter 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.AfterDelimiter are:

• Text.BeforeDelimiter
• Text.BetweenDelimiters

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