Power QueryExercises

Split columns and extract text by delimiter

Split and extract text in a way that works for values of any length.

~9 min read · beginner · Module 6: Text transformations

By Tahir Nas · Examples run in our simulator · Published · Updated

Before this: Trim, clean and capitalise text

When one column holds several facts, such as "Jane Smith" or "jane.smith@firm.example", you can split it into new columns or extract just the part you need. Delimiter-based steps work for values of any length; position-based steps break when lengths change.

Split Column by delimiter

let
    Source = #table({"Customer"}, {{"Ana Ruiz"}, {"Ben Okafor"}, {"Cleo"}}),
    Split = Table.SplitColumn(Source, "Customer", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"First Name", "Last Name"})
in
    Split

Result

First NameLast Name
AnaRuiz
BenOkafor
Cleonull

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

The output names list decides how many columns you get. A missing piece becomes null.

Extra pieces are dropped: "Mary Ann Lee" split into two named columns gives Mary and Ann, and Lee is lost. For names, splitting at the last space (Split Column → By Delimiter → Right-most delimiter) is often closer to the intent.

Extract instead of split

Extract functions keep the original column and add only what you ask for:

let
    Source = #table({"Email", "Ticket"}, {{"ana@kites.example", "Re: [TK-104] refund"}, {"ben.o@reels.example", "[TK-221] login"}}),
    Domain = Table.AddColumn(Source, "Domain", each Text.AfterDelimiter([Email], "@"), type text),
    Ref = Table.AddColumn(Domain, "Ref", each Text.BetweenDelimiters([Ticket], "[", "]"), type text)
in
    Table.SelectColumns(Ref, {"Domain", "Ref"})

Result

DomainRef
kites.exampleTK-104
reels.exampleTK-221

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

Text.BeforeDelimiter and Text.AfterDelimiter take an optional index for 'the second @' or 'counting from the end', which covers most real-world patterns without regular expressions.

Padding codes to a fixed width

The reverse problem: codes that lost their leading zeros somewhere upstream. Text.PadStart restores a fixed width:

Table.TransformColumns(#table({"Store"}, {{"7"}, {"42"}, {"105"}}), {{"Store", each Text.PadStart(_, 4, "0"), type text}})

Result

Store
0007
0042
0105

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

Splitting replaces the original column; extracting adds a new one beside it. Prefer extracting when you still need the full value later, for example keeping the whole email address for a mail merge while grouping by domain. Prefer splitting when the original is only a container for the parts, such as "Leeds-04-B" that should become Store, Aisle and Bin.

Where to find this in Excel

Excel for Windows
Home > Split Column > By Delimiter; Add Column > Extract > Text Before Delimiter
What you see
Split Column replaces the column with parts named Column.1, Column.2 and so on; Extract adds a new column and keeps the original.

Paths are for the Power Query editor in Excel for Microsoft 365, checked against Microsoft's documentation on 8 November 2026: Split columns by delimiter (Microsoft Learn); Add a column based on a data type (Power Query) (Microsoft Support).

Common mistakes

  • Splitting names on every space and losing the third word of longer names.
  • Extracting fixed positions from values whose length varies.
  • Splitting with the default column names (Customer.1, Customer.2) and referring to them later.

Excel, Power BI and the simulator

  • Excel and Power BI Desktop: Transform → Split Column offers by delimiter, number of characters, positions, case changes and digit/non-digit transitions. Add Column → Extract offers Length, First/Last Characters, Range and Text Before/After/Between Delimiters.
  • Split Column → By Delimiter has an Advanced option to split into rows instead of columns, which writes a different pattern (Table.ExpandListColumn).
  • The simulator supports Splitter.SplitTextByDelimiter, ByAnyDelimiter, ByEachDelimiter, ByLengths and ByPositions.
Functions in this lesson:Table.SplitColumnText.BeforeDelimiterText.AfterDelimiterText.BetweenDelimitersText.PadStart

Next step

Apply this lesson in the simulator. The exercise is checked against fresh data, so the query has to work for rows it hasn't seen. It's part of Pro.

Practise: Split email addresses into user and domain (logistics)

Practice this

Each exercise runs in the browser and is checked against fresh data, not just the example.