What is Text.BetweenDelimiters and how do we use it?
Text.BetweenDelimiters is a Power Query function in Power BI that 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.
Syntax
The syntax for Text.BetweenDelimiters is below.
Text.BetweenDelimiters(text as nullable text, startDelimiter as text, endDelimiter as text, optional startIndex as any, optional endIndex as any)How to use Text.BetweenDelimiters
I have an example dataset that I will be using in this article, it just contains one column. In this article, we will extract “222” from the first set of brackets.
In Power BI, open the Power Query editor.
Once the Power Query editor has opened, go to ‘Add Column’ > ‘Custom Column’ and a window should open.
What we can do now, is name the column something appropriate and add the below formula into the formula textbox.
Text.BetweenDelimiters([Column1], “(“, “)”)
What this formula is saying, is to select ‘Column1’, start at the first starting bracket and finish at the last bracket. In this case, this gives us “222”.
Press ‘OK’ on the custom column window and see your newly created column!
You can now see that we’ve successfully extracted the text from the first set of brackets. But what if we need the text from the last set of brackets? We can modify our formula to include the ‘startIndex’ and ‘endIndex’.
Repeat the above steps, but this time use the below formula.
Text.BetweenDelimiters([Column1], "(", ")", 1, 0)
Finishing Off
Text.BetweenDelimiters is a great and an easy way to extract text from a string between the passed delimiters. Although it might be a bit complicated depending on where the text is in the string you’re trying to extract, it is a useful function overall.
To learn more, please visit Learn DAX
