Text.SplitAny Function in Power Query M (6 Easy Examples)

Text.SplitAny splits a text value into a list, breaking it apart at every character you list as a separator. Available in Excel (Power Query), Power BI Desktop, and Power BI Service.

If you have text that mixes several delimiters in one string, like commas, semicolons, and pipes all sitting in the same cell, this is the function that pulls the pieces apart in a single step.

Table of Contents

Syntax of Text.SplitAny Function

Text.SplitAny(text as text, separators as text) as list

where

text (required, text). The text value you want to split.
separators (required, text). A single string holding all the delimiter characters. Each individual character in this string is treated as its own separator.

Returns: a list of text values, one for each piece between the separators.

In plain terms, you give it some text and a string of separator characters, and it hands back a list of the chunks in between.

Example 1: Split a string on a single separator

Break a comma-separated color string into its parts.

let
    Source = Text.SplitAny("red,green,blue",",")
in
    Source

Result: {"red", "green", "blue"}

The comma marks each boundary, so you get a three-item list.

Example 2: Split on several different separators at once

Here the text uses a semicolon, a comma, and a pipe between values. Pass all three characters as the separators string.

let
    Source = Text.SplitAny("oak;maple,birch|cedar",";,|")
in
    Source

Result: {"oak", "maple", "birch", "cedar"}

Each of ;, ,, and | is treated as its own delimiter, so the string splits at every one of them.

Example 3: Grab the first piece with an index

Because Text.SplitAny returns a list, you can index into it. Add {0} to take the first item.

let
    Source = Text.SplitAny("49 Harbor Road, Brighton; Sussex",",;"){0}
in
    Source

Result: 49 Harbor Road

The {0} pulls the first element out of the list, so you get a single text value instead of a list.

Example 4: Count the pieces in each row of a column

A common job is counting how many parts a cell splits into. Wrap Text.SplitAny inside List.Count and you have your answer.

Say you have a Products query with a Tags column where each cell mixes commas, slashes, semicolons, and pipes.

Here is the starting data:

Tags
wireless/audio,portable
kitchen;steel\	durable
office,ergonomic/adjustable

Now add a column that counts the tags in each row:

let
    Source = Excel.CurrentWorkbook(){[Name="Products"]}[Content],
    #"Added TagCount" = Table.AddColumn(Source,"TagCount",each List.Count(Text.SplitAny([Tags],",/;|")),Int64.Type)
in
    #"Added TagCount"

List.Count returns how many items the split produced:

Tags	TagCount
wireless/audio,portable	3
kitchen;steel\	durable	3
office,ergonomic/adjustable	3

Each row splits into three tags, regardless of which delimiters it used.

Example 5: Pull the last piece from each row

To grab the trailing token, wrap the split in List.Last.

Say you have a Codes query with a RawCode column where the region is always the last segment, but the separator varies by row.

Here is the starting data:

RawCode
REF-451/north
REF-892,south
REF-733;east

Now add a column that takes the last segment:

let
    Source = Excel.CurrentWorkbook(){[Name="Codes"]}[Content],
    #"Added Region" = Table.AddColumn(Source,"Region",each List.Last(Text.SplitAny([RawCode],"-/,;")),type text)
in
    #"Added Region"

List.Last returns the final element of each split list:

RawCode	Region
REF-451/north	north
REF-892,south	south
REF-733;east	east

Because the function splits on -, /, ,, and ; alike, the region lands as the last item in every row.

Example 6: Consecutive separators keep empty strings

When two separators sit next to each other, the gap between them becomes an empty text value rather than being skipped.

let
    Source = Text.SplitAny("alpha,,beta",",")
in
    Source

Result: {"alpha", "", "beta"}

The two commas have nothing between them, so an empty string "" is placed in the middle of the list.

Things to keep in mind with Text.SplitAny

Each separator character is independent, not a sequence. Text.SplitAny("a-b","-|") splits on - and on | separately. This is the main difference from Text.Split, which treats "-|" as one whole-string delimiter.
Consecutive, leading, or trailing separators keep empty strings (Example 6). To drop the blanks, filter the result: List.Select(Text.SplitAny([Col],",;|"),each _ <> "").
Matching is case-sensitive, and letters are valid separators. Text.SplitAny("RickDeGroot","er") returns {"RickD", "G", "oot"}, splitting on lowercase e and r only, not the uppercase letters.
You cannot pass a multi-character delimiter as a phrase. There is no way to make it treat ", " (comma-space) as a single unit. For a whole-string delimiter, use Text.Split instead.
null text raises an error. The text parameter is not nullable, so guard a blank-prone column with Text.From([Col]) or a ?? fallback before splitting.

Common questions about Text.SplitAny

What is the difference between Text.SplitAny and Text.Split?

Text.Split takes one delimiter that can be several characters and matches it as a whole string. Text.SplitAny takes a string whose every character is a separate possible separator. Use Text.SplitAny when one column mixes delimiters, and Text.Split when you have a single consistent one like " - ".

When should I use Splitter.SplitTextByAnyDelimiter instead?

Splitter.SplitTextByAnyDelimiter returns a function meant to be handed to Table.SplitColumn, and it supports options like QuoteStyle. Text.SplitAny runs immediately and just returns the list. Use Text.SplitAny for splitting one value inline in a custom column or let step, and the Splitter family for the Split Column by Delimiter UI plumbing.

List of All Power Query Functions

Related Power Query Functions / Articles: