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:
