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
SplitResult
| First Name | Last Name |
|---|---|
| Ana | Ruiz |
| Ben | Okafor |
| Cleo | null |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
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
| Domain | Ref |
|---|---|
| kites.example | TK-104 |
| reels.example | TK-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.
Text.BetweenDelimitersText.PadStartNext 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.
- beginner~6 minPro
Split email addresses into user and domain (logistics)
Add the part before @ as User and the part after @ as Domain.
- beginner~6 minPro
Split email addresses into user and domain (software)
Add the part before @ as User and the part after @ as Domain.
- beginner~6 minPro
Split email addresses into user and domain (hospital)
Add the part before @ as User and the part after @ as Domain.
- beginner~6 minPro
Split customer names into first and last name (kite shop)
Split Customer at the space into First Name and Last Name columns.
- beginner~6 minPro
Split customer names into first and last name (bakery)
Split Customer at the space into First Name and Last Name columns.
- beginner~6 minPro
Split customer names into first and last name (bike rental)
Split Customer at the space into First Name and Last Name columns.
- intermediate~6 minPro
Build fixed-width badge numbers (logistics)
Turn employee IDs like E104 into badge numbers like BADGE-000104.
- intermediate~6 minPro
Build fixed-width badge numbers (software)
Turn employee IDs like E104 into badge numbers like BADGE-000104.
- intermediate~6 minPro
Build fixed-width badge numbers (hospital)
Turn employee IDs like E104 into badge numbers like BADGE-000104.
- intermediate~7 minPro
Extract the reference code from email subjects (kitchens)
Each subject line contains a reference like (ref: KT-0042) somewhere in it. Pull out just the code.
- intermediate~7 minPro
Extract the reference code from email subjects (repairs)
Each subject line contains a reference like (ref: KT-0042) somewhere in it. Pull out just the code.
- intermediate~9 minPro
Split a tag list into one row per tag (outdoor shop)
Each product has a semicolon-separated Tags cell. Produce one row per product and tag, trimmed, with no empty tags.
- intermediate~9 minPro
Split a tag list into one row per tag (recipes)
Each product has a semicolon-separated Tags cell. Produce one row per product and tag, trimmed, with no empty tags.