Splitter.SplitTextByDelimiter
Makes a function that cuts text into pieces at every occurrence of a separator, such as a comma.
Group: Splitters, combiners, replacers and comparers. Power Query M has it too: a query that uses it is written as in Power BI.
Syntax
Splitter.SplitTextByDelimiter(delimiter as text, optional quoteStyle as nullable number) as function| Parameter | Type | Description |
|---|---|---|
delimiter | text | The separator, one or more characters. |
quoteStyle | nullable number optional | QuoteStyle.Csv (the default): a delimiter inside double quotes does not split, and the quotes are taken off; QuoteStyle.None: quotes are ordinary characters. |
Returns
function. A splitter: given a text, the list of the pieces between the separators.
Remarks
- A splitter is a function of one text that gives a list of texts.
Table.SplitColumnandTable.FromListtake one; you can also call it yourself, as the examples do. - Two separators in a row give an empty piece between them; a text without the separator gives a list of one.
- Quotes count by default: a separator between double quotes is part of the piece. Pass
QuoteStyle.Nonewhen quotes are just characters in the data.
Examples
A list of shops
Splitter.SplitTextByDelimiter(",")("Oslo,Bergen,Trondheim"){"Oslo", "Bergen", "Trondheim"}A quoted name that holds the separator
Splitter.SplitTextByDelimiter(",")("""Hansen, Kari"",42,Oslo"){"Hansen, Kari", "42", "Oslo"}The comma inside the quotes doesn't split, and the quotes are taken off.
Split a code column into three
let
Codes = #table({"Code"}, {{"NO-0150-OSL"}, {"SE-11122-STO"}})
in
Table.SplitColumn(Codes, "Code", Splitter.SplitTextByDelimiter("-"), {"Country", "Postcode", "City"})| Country | Postcode | City |
|---|---|---|
| NO | 0150 | OSL |
| SE | 11122 | STO |
Related functions
Splitter.SplitTextByAnyDelimiter, Splitter.SplitTextByEachDelimiter, Table.SplitColumn, Text.Split, Combiner.CombineTextByDelimiter.