If you want to split text using a list of delimiters where each one makes a single cut in order, Splitter.SplitTextByEachDelimiter is the function for that.
It does not split on every occurrence of a delimiter. It uses each delimiter in your list once, in sequence, and returns the pieces as a list. Available in Excel (Power Query), Power BI Desktop, and Power BI Service.
Syntax of Splitter.SplitTextByEachDelimiter Function
Splitter.SplitTextByEachDelimiter(delimiters as list, optional quoteStyle as nullable number, optional startAtEnd as nullable logical) as function
where
delimiters(required, list). The list of text delimiters to split on. Each delimiter is used once, in the order you list them.quoteStyle(optional, nullable number). How quoted text is handled. PassQuoteStyle.Csvto protect a delimiter that sits inside a"..."quoted run (and strip the wrapping quotes). Omit it or passQuoteStyle.Noneto treat quotes as ordinary characters.startAtEnd(optional, nullable logical). Passtrueto scan for delimiters from the right end of the text instead of the left. Omitted orfalsescans from the start.
Returns: a function. That returned function takes a text value and gives back a list of text, split at each delimiter in the list in sequence.
In plain terms, calling Splitter.SplitTextByEachDelimiter does not split anything on its own. It hands you a splitter, and you call that splitter on your text to get the list of pieces.
Example 1: Split on two different delimiters in order
You have the text Order - Status: Shipped and you want three pieces, cutting first at - and then at : .
Splitter.SplitTextByEachDelimiter({" - ",": "})("Order - Status: Shipped")
Result: {"Order", "Status", "Shipped"}
The - delimiter makes the first cut, then : cuts what is left. Two delimiters, two cuts, three pieces.
Example 2: The same delimiter listed twice cuts only twice
Here the text A-B-C-D has three dashes, but the delimiter list only has two.
Splitter.SplitTextByEachDelimiter({"-","-"})("A-B-C-D")
Result: {"A", "B", "C-D"}
Each "-" in the list is used once. The first and second dashes get cut, but the third dash is left alone because the list ran out of delimiters. That is the key difference from Splitter.SplitTextByDelimiter, which splits on every dash and gives you {"A","B","C","D"}.
Example 3: Start cutting from the right with startAtEnd
This time you want the cuts to start at the rightmost dash. Pass QuoteStyle.None for the middle argument and true for startAtEnd.
Splitter.SplitTextByEachDelimiter({"-","-"},QuoteStyle.None,true)("A-B-C-D")
Result: {"A-B", "C", "D"}
The first delimiter cuts at the last dash, the second cuts at the next dash to its left, and the leftmost dash is left intact. The pieces come back in their original left-to-right order.
Example 4: Protect a delimiter inside quotes with QuoteStyle.Csv
The text Reed,"Sales, EMEA",Active has a comma inside the quoted "Sales, EMEA" that you do not want to split on. QuoteStyle.Csv protects it.
Splitter.SplitTextByEachDelimiter({",",","},QuoteStyle.Csv)("Reed,""Sales, EMEA"",Active")
Result: {"Reed", "Sales, EMEA", "Active"}
The first "," cuts after Reed. The second "," skips the comma inside the quotes and cuts after the closing quote instead, and the wrapping quotes are stripped from the result.
Example 5: Split a column into three with Table.SplitColumn
The real-world use is passing the splitter to Table.SplitColumn. You have a Record column where each value packs an invoice, a year, and a status together.
Here is the starting data:
| Record |
|---|
| INV-001 / 2024 | Paid |
| INV-002 / 2025 | Pending |
| INV-003 / 2024 | Paid |
Split the Record column on / then | into three named columns:
let
Source = Excel.CurrentWorkbook(){[Name="Example5"]}[Content],
Split = Table.SplitColumn(Source,"Record",Splitter.SplitTextByEachDelimiter({" / "," | "}),{"Invoice","Year","Status"})
in
Split
For each row, / makes the first cut and | makes the second, producing three values that land in the three new columns.
The result keeps:
| Invoice | Year | Status |
|---|---|---|
| INV-001 | 2024 | Paid |
| INV-002 | 2025 | Pending |
| INV-003 | 2024 | Paid |
The splitter returns a list per row, and Table.SplitColumn spreads each list across the Invoice, Year, and Status columns.
Things to keep in mind with Splitter.SplitTextByEachDelimiter
- Each delimiter fires exactly once, in list order. So
Ndelimiters give you at mostN+1pieces, and a delimiter that appears again later in the text is left intact (Example 2). This catches most people out the first time. - The bare call returns a function, not a list.
Splitter.SplitTextByEachDelimiter({"-"})is a splitter. You have to apply it to text, likeSplitter.SplitTextByEachDelimiter({"-"})("a-b"), to get pieces. startAtEndreverses the cut order, not the output order. Withtrue, the first delimiter cuts at the rightmost match, but the pieces still come back in their original left-to-right order (Example 3).quoteStylecontrols both protection and quote stripping.QuoteStyle.Csvskips a delimiter sitting inside a"..."run and removes the surrounding quotes.QuoteStyle.Nonetreats quotes as plain characters and splits straight through them.- To split on every occurrence of one delimiter, use a different function. Reach for
Splitter.SplitTextByDelimiterwhen you want one delimiter applied everywhere it appears, not consumed once.
Common questions about Splitter.SplitTextByEachDelimiter
What is the difference between Splitter.SplitTextByEachDelimiter and Splitter.SplitTextByDelimiter?
Splitter.SplitTextByEachDelimiter takes a list of delimiters and uses each one once, in order. Splitter.SplitTextByDelimiter takes a single delimiter and splits on every occurrence of it.
Where would I actually use this in a query?
Most often you pass it to Table.SplitColumn (Example 5) to break one column into several, when each piece is separated by a different delimiter and you want exactly one cut per delimiter.
List of All Power Query Functions
Related Power Query Functions / Articles:
