Downloads
Power Query MSplitters and combinersIntermediate

Splitter.SplitTextByEachDelimiter

Returns a splitter that cuts text at each of several delimiters in turn, using a list of delimiter values.

Syntax
Splitter.SplitTextByEachDelimiter(delimiters, optional quoteStyle, optional startAtEnd)

Returns: A function that you pass to a split step. It returns a list of pieces.

What it does

Splitter.SplitTextByEachDelimiter works like Splitter.SplitTextByDelimiter, but takes a list of delimiters. Power Query applies them one after another, so the text is cut at each one in turn. Use it when a text field can be separated by more than one kind of mark, such as a mix of commas and semicolons in a memo.

Syntax and arguments

Example

A memo field from an older system holds several references separated by a mix of semicolons and commas. The team needs each reference on its own line.

let
    Memo = "INV-4471;INV-4472,INV-4475",
    Pieces = Splitter.SplitTextByEachDelimiter({";", ","})(Memo),
    Trimmed = List.Transform(Pieces, Text.Trim)
in
    Trimmed

Pieces is the list of three references: INV-4471, INV-4472 and INV-4475. The second pair of brackets calls the splitter on the memo. Trimming removes any stray spaces.

Example: a second, different use

Splitting a column in a table with Table.SplitColumn(Source, "Memo", Splitter.SplitTextByEachDelimiter({";", ","})), which creates one column per piece.

Things to watch

In business terms

Memo and reference fields that hold several invoice or check numbers are common in receivables and cash application. Splitting them into one row per reference lets each invoice be matched to a payment.

Related functions

All Power Query M functions · View as Markdown · M basics