Power Query Editor: How to Split Text into Multiple Columns

In Excel for Microsoft 365 on Windows, select the column in Power Query Editor, then choose Home > Split Column > By Delimiter. Pick the delimiter and whether to split at its first, last, or every occurrence; choose columns or rows if needed, then select Home > Close & Load to return the result to Excel.

What “Text to Columns” Means in Power Query

Excel’s worksheet command Text to Columns and Power Query’s Split Column feature address a similar problem: dividing one field into separate pieces. They work differently, though. Text to Columns changes cells in a worksheet, while Power Query records a transformation in a query. The source data remains the input, and the split is applied when the query runs or refreshes.

That distinction matters when data is imported regularly. A one-time worksheet cleanup may suit Text to Columns. If you bring in a new file each week and want the same split applied every time, Power Query is usually more suitable. Once the transformation is set up, refresh the query to process the current source data using the same steps.

Power Query is useful for more than comma-separated values. It can split names, product codes, dates stored as text, file paths, or other fields with a consistent separator. It also provides ways to split by character position or by transitions between letters and digits, depending on the pattern in the data.

How the Split Column Command Works

A delimiter is the character or text sequence that marks a boundary. In a field such as North,West,Central, commas separate the three region names. Splitting at every comma creates three output fields. Splitting only at the left-most comma creates two: North and West,Central. The delimiter itself is normally removed from the resulting pieces.

In the editor, the split becomes an Applied Step in the query. Power Query preserves the order of steps, so later steps operate on the output of earlier ones. For example, you can split a column, rename the resulting columns, remove an unwanted field, and set data types. You can review, rename, or delete a step in the Query Settings pane; changing an earlier step can affect later ones.

The usual command replaces the selected column with the split result, which may contain multiple columns or rows. If you need to keep the original whole value too, duplicate the column before splitting: right-click its header and choose Duplicate Column. This gives you both the untouched text and the parsed fields.

Step-by-Step: Split by a Delimiter

In Excel, open the query using Data > Queries & Connections, then double-click the query. You can also create or open a query through the Get Data commands on the Data tab.
In Power Query Editor, select the header of the column to split. Selecting the header makes clear which field the transformation will affect.
Choose Home > Split Column > By Delimiter. The same Split Column command is also available on the Transform tab.
In the dialog, choose a listed delimiter or enter a custom one, such as a comma, slash, hyphen, or a sequence of characters. Review the preview before applying the split.
Choose how to split: at the left-most delimiter, at the right-most delimiter, or at each occurrence. Use the advanced options when you need to split into rows rather than columns, if that option is available in the dialog.
Select OK. Check the resulting columns and their values, then rename generic headings such as Column1 and Column2 to meaningful names.
Choose Home > Close & Load to load the result, or use the Close & Load menu to choose a destination or connection-only option where available.

For instance, if a column contains INV-2026-084, splitting at each hyphen produces INV, 2026, and 084. If the aim is only to separate the prefix from the rest of the code, split at the left-most hyphen instead. That creates two fields and avoids unnecessary extra columns.

Choose the Right Split Option

The split setting should match the structure you want, not simply the first pattern that looks convenient. A quick guide:

Left-most delimiter: Use this when the first separator divides a leading label from everything after it. For Smith, Jordan, Jr., it separates Smith from the remaining text.
Right-most delimiter: Use this when the final separator marks the boundary of interest, such as separating a file name from a final extension-like suffix. Check for multiple periods or other exceptions before relying on it.
Each occurrence: Use this when every separator represents the same boundary, such as a code made of several hyphen-separated parts.
Split into rows: Use this when one source record contains a list of values and each value should become its own record. For example, splitting Red;Blue;Green into rows creates one color per row.

Splitting into rows has an important effect: other columns in the same record are repeated for each resulting value. If the original row has an order ID and three semicolon-separated tags, the output will normally have three rows carrying that order ID. This is often helpful for analysis, but it increases the row count and may change what a later total or count means.

When the delimiter is not consistent, consider whether a different method is more reliable. Split by positions can work for fixed-width codes, while splitting by digit-to-non-digit transitions may help parse a pattern such as AB123CD. A rule based on position or character type can be less fragile than guessing a delimiter, but it still needs to fit all legitimate values.

Examples of Common Splits

Separating first and last names: A space delimiter can split Maya Chen into two fields. But a simple split at every space can create extra columns for names with middle names, multiple given names, or suffixes. If the data does not follow one consistent naming pattern, do not assume that the first two resulting columns always represent first and last name. Inspect the exceptions and choose a rule that reflects the actual data.

Parsing a path or code: A slash can separate folder levels in a path, while a hyphen can divide a product code into components. Before splitting, check whether the same character has another meaning in some values. A hyphen in a descriptive product name, for example, may not mark the same kind of boundary as a hyphen in a structured code.

Turning a list into records: Suppose a row has an account number and a cell containing email;phone;mail. Splitting the list into rows can produce three records, each associated with that account number. This is often easier to filter or count than keeping a list inside one cell. If order matters, check that the resulting sequence is preserved and do not sort the values unless that is intentional.

Extracting an extension: A right-most period can separate a file name from its final suffix, but a name such as backup.2026.xlsx illustrates why the left-most and right-most choices differ. The right-most period separates backup.2026 and xlsx; the left-most would separate backup from 2026.xlsx.

Check Types, Headers, and Refresh Behavior

After splitting, inspect the data rather than assuming the preview is correct. Power Query may assign or infer data types through a separate Changed Type step. A component that looks numeric may need to remain text if leading zeros are meaningful: splitting a code such as 0042-A should not turn 0042 into the number 42 if the zeros are part of the identifier. Select the relevant column and set its type deliberately from the data type control in the editor.

Also check whether the first row contains headers. If a split creates new columns, the output may receive generic names or automatic type-detection steps. Rename the fields, inspect any Changed Type step, and verify the final table in Excel. If a later step refers to an old column name, renaming or removing that column can cause an error.

Power Query stores the transformation instructions, not a guarantee that all future source values will have the same pattern. After refreshing, a new value with no delimiter may remain as one piece, while a value with more delimiters than expected may create additional pieces or fail to match the downstream layout. Review the query output when source formats change. In Excel for Windows, Data > Refresh All refreshes workbook queries; a query can also be refreshed from its context menu in Queries & Connections.

Common Problems and How to Fix Them

No split appears: Confirm that the selected delimiter exactly matches the data. A regular space and a nonbreaking space can look alike but are different characters. If imported text contains inconsistent whitespace, clean or replace it before splitting.
Too many columns appear: The delimiter may occur within values as well as between them. Use a first- or last-occurrence option, or choose a more specific separator. If the data uses quoted fields, check the source-import settings and any quote-handling option exposed by the split dialog rather than assuming every comma is a boundary.
Blank output fields appear: Consecutive delimiters or a delimiter at the beginning or end of a value can create empty pieces. Decide whether those blanks are meaningful. You may need to replace the source pattern, filter empty values, or handle them in a later step.
Refresh produces errors: A later step may expect a particular column name, type, or number of split pieces. Review Applied Steps from top to bottom and inspect the first step that displays an error. Adjust the split rule or later steps to accommodate valid variations in the source.
Values lose leading zeros: Change the affected result column to Text. Identifiers are often better represented as text even when they contain only digits.

Power Query Split Column vs. Excel Text to Columns

Text to Columns is a worksheet operation, usually used for a quick one-time conversion of selected cells. Power Query is a repeatable data-preparation process. If you edit a worksheet directly with Text to Columns, the cells themselves change. In Power Query, the source is read and the query creates a transformed result when loaded; refresh reruns the recorded steps against the current input.

Choose Text to Columns when the data is already in a worksheet, the cleanup is one-off, and you do not need a repeatable import. Choose Power Query when the data comes from files, tables, or other connected sources and the same cleanup will be needed again. Power Query can also combine splitting with filtering, removing rows, changing types, and other preparation steps before the result reaches a worksheet or data model.

Version and Platform Notes

The instructions above describe Excel for Microsoft 365 on Windows. Excel for Mac has Power Query capabilities, but available connectors and editing features can differ by Excel release and update channel; menu placement may also differ. If a particular split option is missing, check the editor available in that version or use Excel for Windows for the steps described here. Excel for the web has its own feature availability and may not expose the same full editing workflow for every query or source, so do not assume that a Windows ribbon path will appear unchanged in a browser.

Power Query is also present in other Microsoft products, including Power BI, but this article describes Excel. The editor concepts are similar, though the route for opening a query and loading its output depends on the host application.

Frequently Asked Questions

Can Power Query split a column into rows instead of columns?
Yes. In the By Delimiter dialog, use the advanced split options to choose rows when that choice is available. Each separated value becomes a row, and the other fields from the original record are repeated alongside it.

How do I split a column at only the first delimiter?
Select the column, choose Home > Split Column > By Delimiter, select the delimiter, and choose the left-most delimiter option. This leaves any later occurrences in the second result rather than creating more columns.

Can I keep the original column after splitting?
Yes. Duplicate the column before applying Split Column, then split the copy. The original field remains available for reference, comparison, or later output.

Does Power Query change my source data?
Normally, no. A query applies steps to data it reads and loads a result to the destination you choose. Refreshing updates the query result; it does not turn the transformation into a direct edit of the original source file.

Why does my split work for some rows but not others?
The values may use different separators, have extra separators, contain blank pieces, or include characters that look similar but are not identical. Inspect the source values and preview, then choose a delimiter rule that covers the real patterns or clean the inconsistent values before splitting.

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.