Text.SplitAny is a Power Query M function that splits a text value into a list of text values based on any character in a specified list of delimiters. The function returns a list of text values resulting from the split operation.
Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365
Syntax
Text.SplitAny(
text as text,
separators as text,
) as listDescription
Returns a list of text values resulting from the splitting a text value text based on any character in the specified delimiter, separators.
The Text.SplitAny function works on a ‘text’ input, that’s the string you’d like to dissect, and ‘separators’, which are the delimiters for the split. The result is a list of text values, each part representing a piece of the original string.
Examples
To understand the Text.SplitAny function, let’s dive into some practical examples.
Suppose you have a phone number “06-18-18-22” and you want to split it into separate parts. Here’s how you can use Text.SplitAny with a single delimiter:
Text.SplitAny( "06-18-18-22", "-" )
In this instance, the function takes “-” as the delimiter and splits the phone number into individual sections.
What if your string has multiple separators? You can provide Text.SplitAny with a string that contains all characters. That’s different from most other functions that require a list of separators. Here’s an example:
Text.SplitAny( "6|8|8-2", "-|" )
When providing “-|” in the separators argument, the function uses both “-” and “|” as separators to split the string.
The function can also split the string with less obvious positions. Consider a name string “RickDeGroot”, you can split it at “e” and “r”:
Text.SplitAny( "RickDeGroot", "er" ) // Returns { "RickD", "G", "oot" }In conclusion, Power Query M’s Text.SplitAny function helps dissecting strings based on one or multiple delimiters.
Related articles
Learn more about Text.SplitAny 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.SplitAny are:
