Splitter.SplitTextByDelimiter in Power Query M (6 Examples)

Splitter.SplitTextByDelimiter returns a function that splits text into a list of text values at the delimiter you specify.

If you want to split text at a delimiter in Power Query, or break one column into several, this is the splitter you’ll reach for.

The part that trips people up: it does not split anything by itself. It hands you a splitter function, and that function does the actual splitting. Example 1 shows exactly how this works.

Table of Contents

Syntax of Splitter.SplitTextByDelimiter Function

Splitter.SplitTextByDelimiter(delimiter as text, optional quoteStyle as nullable number, optional csvStyle as nullable number) as function

where

delimiter (required, text). The delimiter to split at. It can be one character or several, such as ", " or " - ".
quoteStyle (optional, nullable number). A QuoteStyle value. QuoteStyle.Csv ignores delimiters inside double-quoted sections and strips the quotes from the output. QuoteStyle.None splits at every delimiter and treats quote marks as ordinary text.
csvStyle (optional, nullable number). A CsvStyle value. CsvStyle.QuoteAfterDelimiter honors a quote mark only when it comes right after a delimiter. CsvStyle.QuoteAlways honors it anywhere in the field. You will rarely need this one.

Returns: a function. The returned function takes a text value and splits it into a list of text values at the specified delimiter.

In plain terms, you tell it what to split on, and it hands you back a splitter. That splitter does the actual cutting when you apply it to text.

Example 1: Create a splitter and apply it to text

Split the invoice number INV-2024-0067 at each hyphen. The first step builds the splitter, the second step applies it.

let
    SplitByDash = Splitter.SplitTextByDelimiter("-"),
    Source = SplitByDash("INV-2024-0067")
in
    Source

Result: {"INV","2024","0067"}

SplitByDash holds a function, not text. Calling it on the invoice number is what produces the list. You can also write it in one line: Splitter.SplitTextByDelimiter("-")("INV-2024-0067").

Example 2: Keep quoted text together with QuoteStyle.Csv

An order line lists three products, and one product name contains a comma: Laptop,"Mouse, Wireless",Keyboard.

In the M code below, the quote marks inside the string are doubled (""). That is how M escapes a quote inside a text literal, so the code stays copy-pasteable.

let
    Source = Splitter.SplitTextByDelimiter(",",QuoteStyle.Csv)("Laptop,""Mouse, Wireless"",Keyboard")
in
    Source

Result: {"Laptop","Mouse, Wireless","Keyboard"}

The comma inside the quotes does not cause a split, and the quote marks are removed from the output. Three clean items.

Example 3: See what QuoteStyle.None does to the same text

Same input, but with QuoteStyle.None the quote marks mean nothing.

let
    Source = Splitter.SplitTextByDelimiter(",",QuoteStyle.None)("Laptop,""Mouse, Wireless"",Keyboard")
in
    Source

Result: {"Laptop","""Mouse"," Wireless""","Keyboard"}

Now you get four items. The split happens at every comma, even the one inside the quotes.

The quote marks stay in the text as literal characters, so the middle items are "Mouse and Wireless" (note the leading space). If you ever need this mode, Text.Trim can clean up those leading spaces afterward.

Example 4: Split a column into multiple columns

This is the real job of Splitter.SplitTextByDelimiter. It goes in as the third argument of Table.SplitColumn, which is exactly what Power Query writes when you use Split Column by Delimiter.

Say you have course codes in one column and want the subject, number, and term in their own columns.

Here is the starting data:

Course Code
MATH-201-Fall
HIST-110-Spring
BIO-345-Summer

Split the Course Code column at the hyphens:

let
    Source = Excel.CurrentWorkbook(){[Name="Example4"]}[Content],
    Split = Table.SplitColumn(Source, "Course Code", Splitter.SplitTextByDelimiter("-"), {"Subject", "Number", "Term"})
in
    Split

The splitter cuts each cell at the hyphens, and the list at the end names the new columns.

The result:

Subject	Number	Term
MATH	201	Fall
HIST	110	Spring
BIO	345	Summer

Each course code becomes three columns, split at every hyphen.

Example 5: Split CSV records that contain quoted commas

Now for a case you’ll actually run into: a column holds raw CSV lines, and some fields are quoted because they contain commas.

Here is the starting data:

Record
Olivia Reed,”Manager, Sales”,Chicago
Dev Patel,”Analyst, Finance”,Boston
Lena Fischer,Engineer,Austin

Split the Record column at the commas, with QuoteStyle.Csv protecting the quoted job titles:

let
    Source = Excel.CurrentWorkbook(){[Name="Example5"]}[Content],
    Split = Table.SplitColumn(Source, "Record", Splitter.SplitTextByDelimiter(",",QuoteStyle.Csv), {"Name", "Title", "City"})
in
    Split

The result:

Name	Title	City
Olivia Reed	Manager, Sales	Chicago
Dev Patel	Analyst, Finance	Boston
Lena Fischer	Engineer	Austin

Manager, Sales stays in one column because of QuoteStyle.Csv, and the quote marks are gone from the output.

Example 6: See what happens when the delimiter is not found

Split a plain sentence at a semicolon that is not there.

let
    Source = Splitter.SplitTextByDelimiter(";")("Q3 budget review")
in
    Source

Result: {"Q3 budget review"}

No error. You always get a list back, and when the delimiter never appears, the whole text is its only item.

Things to keep in mind with Splitter.SplitTextByDelimiter

Seeing the word function in the preview pane? You built the splitter but never applied it. Call it on text, or pass it to Table.SplitColumn.
The splitter only accepts text. Applying it to a number throws an error. Convert with Text.From, or set the column type to text first.
Don’t rely on a default quoteStyle. Microsoft doesn’t document one. When your text contains quoted delimiters, pass QuoteStyle.Csv explicitly, which is what the Power Query UI’s generated code does too.
Multi-character delimiters are fine. Unlike Excel’s Text to Columns, the delimiter can be a whole string like ", " or " - ".
Empty text still returns a list. Splitting "" gives {""}, not an empty list.

Common questions about Splitter.SplitTextByDelimiter

Can I split at more than one delimiter?

Not with this function, it takes exactly one delimiter. Use Splitter.SplitTextByAnyDelimiter to split at any of several delimiters, or Splitter.SplitTextByEachDelimiter to split at a sequence of delimiters in order.

What is the difference between Splitter.SplitTextByDelimiter and Text.Split?

Text.Split splits a text value right away and returns the list. Splitter.SplitTextByDelimiter returns a reusable splitter function, which is what Table.SplitColumn expects. Text.Split also has no quote handling.

Can I apply one splitter to a whole list of values?

Yes. The splitter is an ordinary function, so you can pass it to List.Transform to split every text value in a list in one go.

What if I only need the text between two delimiters?

Then you don’t need to split at all. Text.BetweenDelimiters extracts just the text between a start and an end delimiter, which is simpler than splitting and picking an item.

List of All Power Query Functions

Related Power Query Functions / Articles: