Power Query Splitter Functions and When to Use Them

Formulas & functions · Updated November 22, 2026

Power Query splitter functions divide text at delimiters, specified characters, or other boundaries. For a simple exact separator, use Text.Split; for a reusable split operation in a table transformation, use a function from the Splitter library, such as Splitter.SplitTextByDelimiter. The right choice depends on whether you need a list, a table column split, or more specialized handling such as quoted CSV text.

What Power Query splitter functions do

Power Query uses the M language to transform data. Its text-splitting tools take a text value and identify boundaries, then return the resulting pieces. Those pieces might be a list of text values, or—when used in a table operation—separate columns or rows. Splitting does not inherently understand what a piece of text means: it follows the boundary rule you specify.

For example, the value West-Region-04 contains two hyphens. Splitting at every hyphen produces three pieces: West, Region, and 04. If your actual goal is only to keep the part before the first hyphen, a function such as Text.BeforeDelimiter is more direct. Choosing a targeted operation can prevent unnecessary columns and make the transformation easier to read.

It is useful to distinguish two kinds of tools:

  • Text functions, including Text.Split, return or extract text values and lists.
  • Splitter functions, including Splitter.SplitTextByDelimiter, define a splitting rule that can be passed to a table operation such as Table.SplitColumn.

In Excel for Microsoft 365 on Windows, you can apply many common splits through the Power Query Editor ribbon, or write the equivalent M expression in the formula bar or Advanced Editor. The interface generates M steps behind the scenes.

Core text functions: Text.Split and Text.SplitAny

Text.Split(text, separator) divides a text value wherever the exact separator string occurs and returns a list. The separator can be more than one character. For instance, splitting on || treats the two-character sequence as one delimiter; it does not split at each individual vertical bar.

Text.Split("red||green||blue", "||")

This returns a list containing red, green, and blue. A list is not automatically the same thing as three table columns. In a query, you can expand a list into rows, or use a table-splitting step when you need columns.

Text.SplitAny(text, separators) has a different rule: every character in the separators argument is treated as a possible boundary. For example, Text.SplitAny("red,green;blue", ",;") splits at either a comma or a semicolon. It does not treat the string ,; as a single two-character delimiter. This function is useful when a source inconsistently uses several single-character separators.

Repeated delimiters and delimiters at the beginning or end can create empty pieces. Those empty strings are distinct from null values and from whitespace-only text. If the source may contain consecutive separators, inspect the output before assuming every piece contains a value.

Extracting part of a value with delimiter functions

When you do not need every segment, delimiter extraction functions can be simpler than splitting a value and then selecting one item. Text.BeforeDelimiter returns text before a delimiter, while Text.AfterDelimiter returns text after it. With the default occurrence, each uses the first matching delimiter.

Text.BeforeDelimiter("INV-2026-0042", "-")

The result is INV. The corresponding Text.AfterDelimiter expression with the same input and delimiter returns 2026-0042, because it keeps everything after the first hyphen. This is a common point of confusion: “after” does not mean “after the last delimiter” unless you specify an occurrence accordingly.

Text.BetweenDelimiters extracts text between a starting and ending delimiter. For example, Text.BetweenDelimiters("Order [A17] paid", "[", "]") returns A17. These functions also support optional occurrence arguments for handling repeated delimiters. Occurrence indexing and relative-position options are worth checking carefully when a value has several matching boundaries; test the expression against representative records, including unusual ones.

Extraction functions can be convenient for a single field, such as taking a file extension after a period or an ID between brackets. They do not, by themselves, change the number of columns in a table. Use them in a custom column or a transformation of an existing column when the result should remain one value per row.

Splitter library functions and table columns

The Splitter library provides functions that create a splitting rule. A typical pattern is to pass that rule to Table.SplitColumn. For example, the following step splits a column called Code wherever a hyphen appears:

Table.SplitColumn(Source, "Code", Splitter.SplitTextByDelimiter("-", QuoteStyle.None), {"Code.1", "Code.2", "Code.3"})

Here, Source is the previous table step, Splitter.SplitTextByDelimiter defines the delimiter rule, and the final list gives names for the resulting columns. The number and names of output columns should suit the expected data. If records have different numbers of segments, decide how the query should handle missing or additional pieces rather than assuming every row has the same shape.

Other useful options include Splitter.SplitTextByAnyDelimiter, which accepts a list of delimiters, and Splitter.SplitTextByWhitespace, which splits on whitespace. Splitter.SplitTextByEachDelimiter applies a sequence of delimiters, and Splitter.SplitTextByCharacterTransition can split at a change between character categories. These are more specialized than a simple exact delimiter, so use them when their particular boundary rule matches the source data.

Some splitter functions accept a quote-style argument. In CSV-like text, QuoteStyle.Csv can account for quoted fields so that a delimiter inside a quoted field is not treated like an ordinary boundary. QuoteStyle.None does not apply that CSV quoting behavior. This matters when a field such as "Smith, Jane" contains a comma that belongs to the value rather than separating two fields.

Splitting through the Power Query Editor

For most routine work in Excel for Microsoft 365 on Windows, the ribbon is the quickest way to create a split:

  1. Load the data into Power Query, then select the column to divide.
  2. In the Power Query Editor, choose Transform > Split Column.
  3. Choose a split method, such as By Delimiter, By Number of Characters, By Positions, or By Character Transition, depending on the available option and your data.
  4. For a delimiter split, select or enter the separator and choose whether to split at the left-most delimiter, right-most delimiter, or each occurrence. Choose whether the results should go into columns or rows where the dialog offers that setting.
  5. Review the preview, confirm the split, and check the generated step and column names before loading the result.

The exact dialog options depend on the split method. Character-transition splitting is helpful for values such as ItemCode, where a boundary occurs between lowercase and uppercase letters, or strings where digits meet non-digits. It is not a substitute for a known delimiter if the source has irregular capitalization or mixed formatting.

On Mac, Power Query availability and Editor capabilities can vary by Excel version and build. When the Editor provides the corresponding transformation, the underlying M concepts are the same, but the ribbon layout or available connectors may differ. If a particular ribbon choice is unavailable, a custom M step may offer another route, subject to the functions supported by that Excel build.

Choosing the right method

Start by describing the boundary precisely. Is it an exact sequence, any one of several characters, a fixed character position, or a transition between character types? Then decide the shape of the result: do you want one extracted text value, a list, multiple columns, or multiple rows?

  • Use Text.Split for an exact separator when a list result is useful.
  • Use Text.SplitAny when any of several individual characters should separate the text.
  • Use Text.BeforeDelimiter, Text.AfterDelimiter, or Text.BetweenDelimiters when you need only a portion of a value.
  • Use a Splitter function with Table.SplitColumn when the transformation should create table columns according to a reusable rule.
  • Use the Editor’s split-by-rows option when each piece should become its own record rather than a separate field in the same row.

Consider whether the delimiter is guaranteed to be unique. Splitting a full name on a space, for example, may behave poorly if some people have middle names or compound surnames. A delimiter rule is only as reliable as the assumptions about the source. If a file format has a proper parser, importing it as a structured file is generally safer than treating its raw lines as ordinary text.

Common problems and how to avoid them

Unexpected empty values: Consecutive delimiters, or one at the start or end, can yield empty segments. Decide whether to retain, remove, or replace those results. Do not automatically remove empty strings if their positions carry meaning, such as an intentionally blank field in a delimited record.

Whitespace remains around a segment: A delimiter split does not necessarily trim spaces around the resulting pieces. If a source contains North, South, the second value may begin with a space. Apply a trim transformation to the resulting text columns when appropriate, and verify that trimming will not damage meaningful characters.

Different rows have different segment counts: A fixed set of output columns may leave blanks for shorter values or fail to represent longer ones as intended. Inspect the longest and shortest records, then choose a policy: keep extra text together, split into rows, or normalize the source into a consistent structure.

A delimiter inside quoted text causes a bad split: Plain text splitting treats the delimiter as a boundary even when it appears inside quotes. For actual CSV data, use a CSV-aware import or an appropriate splitter with CSV quote handling instead of a basic split rule. Merely choosing a comma as the delimiter does not make a transformation CSV-aware.

Nulls or non-text values cause errors: A text function expects text. If a column contains nulls or mixed data types, check the earlier type-conversion step and decide how nulls should be handled. Avoid converting values blindly when their original types matter; a date or number may need different treatment from ordinary text.

When a split produces an error, inspect the specific row and the steps immediately before the split. Use a small set of representative records to validate changes, including nulls, empty values, repeated separators, and embedded delimiters. Power Query applies steps on refresh, so a rule that works on today’s sample should also be checked against future source data.

How splitting fits into a refreshable query

A split in Power Query is a recorded transformation, not a one-time edit to the loaded worksheet. When the query refreshes, Power Query reads the source again and applies the split step to the new data. That repeatability is useful for monthly exports or recurring text files, but it makes stable assumptions important: if the supplier changes the delimiter or the field order, the same step may produce a different result or an error.

Review the Applied Steps pane after creating a split. Rename a step when its default name is unclear, and check any later type-change step. Power Query may assign data types after a structural transformation; confirm that values such as codes with leading zeros remain text. If a code like 0048 is converted to a number, the leading zeros can disappear even though the split itself worked correctly.

Frequently Asked Questions

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

Text.Split immediately splits one text value and returns a list. Splitter.SplitTextByDelimiter creates a delimiter-based splitting function, commonly passed to Table.SplitColumn to divide a table column into columns. They use related delimiter logic but serve different transformation patterns.

How do I split text at either a comma or a semicolon?

For a single text value, use Text.SplitAny with a separators string containing both characters. For a table-column split, use Splitter.SplitTextByAnyDelimiter with a list containing the comma and semicolon. Check the source first if either character can appear inside a field.

Can Power Query split a column into rows instead of columns?

Yes. In the Power Query Editor’s split dialog, choose the option to split into rows when it is available. This creates a separate row for each piece, which is useful for lists stored in one cell. It also changes the row count, so review how other columns should be repeated alongside the new rows.

Does a delimiter split understand quoted CSV fields?

Not by default. A basic text split treats each matching delimiter as a boundary, including one inside quoted text. For CSV input, use the CSV import/parser or a splitter configured with CSV quote handling so commas inside quoted fields remain part of the field value.

How do I split only at the last delimiter?

For extraction, use the optional occurrence or relative-position argument of functions such as Text.BeforeDelimiter or Text.AfterDelimiter to target a delimiter from the end. For a table split, choose the right-most delimiter option in the Editor dialog when available. Test with values containing zero, one, and multiple delimiters before applying the rule broadly.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.