Text.SplitAny - Text Function

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 list

Description

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.

Splitting with a Single Delimiter

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", "-" )

Text.SplitAny with a single delimiter in Power Query M

In this instance, the function takes “-” as the delimiter and splits the phone number into individual sections.

Splitting with Multiple Delimiters

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", "-|" )

Text.SplitAny with multiple delimiters in Power Query M

When providing “-|” in the separators argument, the function uses both “-” and “|” as separators to split the string.

Splitting at Less Obvious Positions

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.

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

Other functions related to Text.SplitAny are:

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