Power Query Split Column by Delimiter: A Practical Guide

To split a column by a delimiter in Power Query for Excel, select the column in Power Query Editor, then choose Home > Split Column > By Delimiter. Pick the delimiter, such as a comma or space, and choose whether to split at every match or only the first or last one; Power Query creates separate columns or rows from the result.

What splitting by a delimiter does

A delimiter is a character or text string that marks a boundary between pieces of information. Commas often separate values in a list, spaces separate words, and a hyphen might separate a product code into a category and a number. Splitting tells Power Query to find that boundary and put the text on either side into different fields.

For example, the value North,Seattle can become North and Seattle when the comma is the delimiter. This operation changes the shape of the query’s data; it does not alter the original worksheet table. The query stores the transformation as a step and reapplies it when you refresh the query.

Delimiter splitting is useful when a source provides several values in one field but you need to sort, filter, group, or analyze those values separately. It is not the same as splitting a worksheet cell with a formula: Power Query transforms data during import or refresh, and its result is loaded back as a table or another supported destination.

Split a column in Excel for Microsoft 365

These steps describe Excel for Microsoft 365 on Windows. If your data is already connected to Power Query, open the query by choosing Data > Queries & Connections and double-clicking it. For a normal worksheet range, first make it an Excel table or select a cell in an existing table, then choose Data > From Table/Range. Excel opens the Power Query Editor.

Select the column header containing the text you want to divide. Selecting the header ensures the operation applies to that whole column.
Choose Home > Split Column > By Delimiter. The same command is also available from the Transform tab.
In the dialog, choose a delimiter from the list, or choose a custom delimiter and enter the character or text to find.
Choose how often to split: at each occurrence, at the left-most delimiter, at the right-most delimiter, or at a specified position where the available options allow it.
Open Advanced options if you need to split into rows instead of columns, or to set a different number of output columns.
Check the preview. If the result looks right, choose OK, rename the new columns if useful, and select Home > Close & Load to return the results to Excel.

Power Query usually names the outputs with the original column name and a numeric suffix, such as Location.1 and Location.2. Rename these to meaningful labels like Region and City. When you load the query, the split is repeated from the source whenever the query refreshes, so you generally do not need to repeat the menu operation for new rows.

On Excel for Mac and Excel for the web, Power Query’s availability, entry points, and editing features can differ from Windows desktop. Use the Power Query Editor where query authoring is supported and look for its Split Column command; do not assume every Windows dialog option is present in every build.

Choose the delimiter and split behavior

The best setting depends on what the delimiter means in the data, not merely on which character appears most often. A comma is suitable for a consistently comma-separated pair, while a hyphen can be suitable for a code such as AB-1042. A space is a less reliable delimiter when some values contain multiple words or repeated spaces.

Each occurrence: Splits wherever the delimiter appears. Use this for a list such as red|blue|green when each item should occupy its own output field.
Left-most delimiter: Splits only at the first match. For Doe, Jane, PhD, this produces Doe and Jane, PhD, which can be useful when the first separator marks the boundary you need.
Right-most delimiter: Splits only at the final match. For a folder-like value such as Archive/2025/Report, it can separate the final item from the preceding path.
Custom delimiter: Lets you enter a character or a multi-character string, such as - or ::. Include surrounding spaces when those spaces are part of the intended boundary.

Power Query treats the delimiter as literal text, not as a pattern or regular expression. If the data uses several different separators, one split operation will not automatically treat all of them as equivalent. You may need to standardize the values first, or perform separate transformations in a deliberate order.

Split into columns or into rows?

Splitting into columns is appropriate when each source row describes one record and the pieces have stable roles. For example, INV-2026-014 could be separated at hyphens into prefix, year, and sequence fields. Those fields can then be typed, filtered, or joined to other tables independently.

Splitting into rows is often better when a cell contains a variable-length list. Suppose a customer row has apples; pears; plums. Splitting into columns would create a set of fields whose number depends on the longest list. Splitting into rows instead gives each item its own row, which is usually easier to count, filter, or relate to a separate item table. In the split dialog, use Advanced options and choose the option to split into rows when it is available.

Think about the structure required by the next task. A fixed pair such as first name and last name normally suits columns. A list of tags, products, or email recipients normally suits rows if you need to analyze each member as an individual value. Splitting into rows can repeat other values from the original record, such as an order number, alongside each new item.

Examples that clarify the choice

Consider a column named FullName containing Jordan Lee. Splitting at each space yields Jordan and Lee for that example, but it does not reliably identify first and last names for people with middle names, compound surnames, or inconsistent spacing. If the data has those cases, a simple space split may create more columns than expected or put name parts in the wrong field. Use it only when the source format is consistent enough for the task.

For an address field such as Vancouver, BC, Canada, splitting at every comma creates three parts, while splitting at the left-most comma creates the city and leaves BC, Canada together. The right choice depends on whether the desired outputs are city, province, and country, or simply city and remainder. If commas can also appear inside quoted address text, inspect the source format carefully rather than assuming every comma is a field boundary.

For a code such as dept::finance, enter :: as the custom delimiter to separate the department label from the value. This illustrates why a delimiter does not have to be one character. A multi-character delimiter is often safer than splitting on a common character that might also occur within the data.

What Power Query records behind the scenes

Power Query saves each transformation as a step in the Applied Steps list. A delimiter split is represented in the query’s M language, which makes the operation repeatable and allows the editor to rebuild the output on refresh. You do not need to write M code to use the menu, but understanding the generated step can help when maintaining a query.

A typical split step uses the Table.SplitColumn function together with a splitter. For example, with a table step named Source and a text column named Code, a comma split can be represented as:

= Table.SplitColumn(Source, "Code", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Code.1", "Code.2"})

The column names in the final list identify the outputs. Power Query’s interface may generate different names or additional steps depending on the source and choices. The QuoteStyle.Csv setting is relevant to how quoted text is interpreted by the splitter; it does not mean that every arbitrary text column should be treated as a correctly formatted CSV file. If the source contains embedded delimiters or quotes, verify the preview and the source’s actual format.

After splitting, Power Query may also assign data types in a later step. Review those types: a piece that looks numeric might need to remain text if leading zeros matter, as with a postal code or an employee ID. Changing a value such as 0047 to a number can remove those zeros.

Common problems and ways to fix them

A delimiter split can produce unexpected output when the source values are inconsistent. Look at several representative rows in the preview, including short, long, blank, and unusual entries, before applying the step. Common issues include:

Too many output columns: The delimiter occurs more often in some values than others. Try splitting only at the left-most or right-most occurrence, or use a more specific custom delimiter.
Blank pieces or leading spaces: A value may begin or end with the delimiter, or include spaces around it. Decide whether those blanks carry meaning. If not, use the appropriate trim or clean transformation after splitting.
Rows with missing delimiters: Such rows may result in a blank value in an output column. Inspect them rather than assuming the split failed; the source may genuinely lack one of the pieces.
Wrong columns after refresh: New source rows may contain a different pattern, such as an extra delimiter. Check the query preview and the split step when refreshed data differs from earlier results.
Errors after changing column names: Later steps may refer to the old output names. Review the Applied Steps list and update dependent steps if a renamed or removed column is referenced.

Do not confuse splitting with trimming. A delimiter split separates text at a boundary, but it may leave spaces next to the boundary in the resulting values. If the source is East, West, the second result can include a leading space. Apply a trim transformation to the resulting text columns when that whitespace is unwanted. Similarly, clean nonprinting characters separately if they came from the source.

When to use another approach

Use Power Query when the transformation should be repeatable, when you are importing or combining data, or when you need to apply the same cleanup each time fresh data arrives. It is especially helpful when a split is one step in a larger preparation process, such as removing blank rows, changing types, and merging tables.

For a one-time worksheet edit, Excel’s Text to Columns feature may be quicker: select the cells and choose Data > Text to Columns. It operates on worksheet cells rather than storing a refreshable Power Query transformation, so it is not a substitute when you want the steps replayed on future source data. For a formula-based result in Microsoft 365 or Excel 2024, functions such as TEXTSPLIT can split text within a worksheet formula, but that is a different workflow and may not suit a query-based import.

Choose based on where the data comes from and how it will be reused. A query is usually the better fit for recurring imports and multi-step preparation. Text to Columns suits a direct, manual worksheet change. A formula can be useful when results need to update directly alongside worksheet values, provided the Excel version supports the function and the formula’s spill behavior fits the layout.

Frequently Asked Questions

Can Power Query split a column at every comma?
Yes. Select the column, choose Home > Split Column > By Delimiter, choose comma, and select the option to split at each occurrence. Check whether commas inside the values are real separators before using this setting.

How do I split only at the first delimiter?
In the By Delimiter dialog, choose the left-most delimiter option. This separates the text at its first matching boundary and leaves later occurrences in the remaining output.

Can I split a column into rows instead of columns?
Yes. Open Advanced options in the split dialog and select the split-into-rows option if it is available in your Power Query build. This is useful for a cell containing a list of items that should become individual records.

Why are there spaces after I split the values?
Spaces beside the delimiter are part of the original text and may remain in the output. Apply a trim transformation to the resulting columns if those spaces are not meaningful.

Does a Power Query split update when the source changes?
Yes, when the query is refreshed and the source includes the changed data. The split is a saved transformation, but newly added values with a different delimiter pattern can still require review.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

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

Spotted an error in this guide? Let us know.