Power Query Text to Columns: Split Data Step by Step

To split text into separate columns in Power Query, select the column in Power Query Editor, then choose Transform > Split Column > By Delimiter. Pick the delimiter, such as a comma or space, choose how to split, and confirm; the new columns remain part of the query and are recreated when the query refreshes.

What “text to columns” means in Power Query

Excel’s familiar Text to Columns command separates values in a worksheet, but Power Query performs a similar operation as a repeatable data-preparation step. It takes one text column and divides its contents at a chosen boundary. For example, a column containing North,Seattle can become two columns, one containing North and the other Seattle.

The distinction matters when the data will be updated. A worksheet conversion changes the cells in the current range. A Power Query split is recorded in the query’s Applied Steps list, so a later refresh applies the same transformation to newly imported rows. Power Query does not usually edit the source file; it transforms data for loading to a worksheet or the Data Model.

A delimiter is the character or text that marks a boundary. Common examples include a comma, semicolon, tab, hyphen, slash, or space. A delimiter can also be a longer string, such as - . If no consistent boundary exists, splitting by delimiter may not be the right approach.

How to split a column by delimiter

Load the data into Power Query. In Excel for Microsoft 365 on Windows, select a cell in the source table and choose Data > From Table/Range. If the data is already in a query, choose Data > Queries & Connections, then open the query for editing.
In Power Query Editor, select the column header you want to divide. Selecting the header helps ensure the transformation applies to the whole column rather than only the active cell.
Choose Transform > Split Column > By Delimiter. The dialog lets you select a common delimiter or enter a custom one.
Choose the split rule. For example, select each occurrence to split at every matching delimiter, or choose the left-most or right-most occurrence to make only one boundary.
Review the preview and any advanced options shown in your Excel build. Depending on the dialog, you can split into columns or rows and may be able to set a quote character or limit the number of resulting columns.
Select OK. Check the resulting columns, rename them if useful, and choose Home > Close & Load to return the result to Excel.

For instance, splitting Jules Morgan at the space, at each occurrence, produces separate name parts. The preview is important: a value such as Jules A. Morgan has two spaces, so splitting at each occurrence produces three parts, not just first and last name.

Choose the right split option

The split rule determines how many boundaries Power Query uses. Choose based on the structure you expect, rather than simply on what works for the first row.

Each occurrence: Use this when every delimiter separates another piece, as in red|green|blue. It can produce several columns if some rows contain more pieces than others.
Left-most delimiter: Use this when the first delimiter separates a prefix from the rest. For example, splitting Order-2026-West at the left-most hyphen gives Order and 2026-West.
Right-most delimiter: Use this when the last delimiter marks the boundary you need. Splitting Order-2026-West at the right-most hyphen gives Order-2026 and West.
Columns or rows: Columns keep each piece alongside the original record. Rows place pieces on separate records, which changes the number and meaning of rows in the result.

Use a specific delimiter where possible. A space is convenient for a simple two-part value, but it is a poor choice if the text can contain multiple spaces or a multiword name. Similarly, splitting a product code at every hyphen may be wrong if some codes use hyphens inside a component.

Worked examples and delimiter choices

Suppose an imported table has a column named Location, with values such as Portland, OR. Splitting by the comma at each occurrence creates two columns. The first holds the city text and the second holds the state abbreviation with a leading space. After the split, select the second column and use Transform > Format > Trim to remove spaces at the beginning and end. Rename the results to City and State.

For a filename such as report.final.xlsx, splitting at every period creates three parts. If the goal is only to separate the extension, split at the right-most period instead. That gives a base name of report.final and an extension of xlsx. This illustrates why the right-most option can be safer when the delimiter is allowed inside the value.

Other practical delimiter choices include:

A tab when fields were exported as tab-separated text.
A semicolon when a system uses semicolons between fields.
A slash for values such as 2026/11/02, if the goal is to separate date components rather than convert the text to a date.
A custom multi-character separator, such as :: , when a single punctuation mark also appears within the data.

Splitting a date string is not the same as converting it to a date type. If the goal is date arithmetic or reliable sorting, it is usually better to parse or change the column to a date type after checking the source format and regional settings.

Split into columns or split into rows?

Splitting into columns is appropriate when each piece is a separate attribute of the same record: a city and state, a code and description, or a first and last name. Each source row remains one row, with additional fields beside it.

Splitting into rows is useful when a cell contains a list of values that should become individual records. For example, a row with customer ID 104 and tags priority;renewal;email can be split at semicolons into three rows. The customer ID is repeated so each tag remains associated with its customer. This changes the row count and can affect later totals, joins, and grouping, so confirm that one row per tag is what the analysis requires.

Do not choose rows just to avoid creating many columns. If the pieces represent fixed attributes, columns generally preserve a clearer table structure. If they represent repeated members of a list, rows are often easier to filter and summarize.

Handle irregular values and common problems

Real-world text is not always uniform. Some rows may have no delimiter, others may have several, and some may contain extra spaces or empty pieces. Inspect the preview and a few representative rows before loading the result. A split that looks correct for a typical value can still mishandle an exception.

Delimiter missing: A value without the selected character cannot be divided at that boundary. Depending on the chosen split and data, the corresponding output may be blank or retain the unsplit text. Check those rows explicitly rather than assuming every record has two parts.
Unexpected number of pieces: Splitting at every occurrence can create extra output columns when a row contains additional delimiters. Decide whether to keep those pieces, use only the first or last boundary, or clean the source first.
Leading or trailing spaces: A delimiter may leave whitespace around a piece. Use Transform > Format > Trim on the affected text columns. Trim removes surrounding spaces; it does not remove spaces inside a phrase.
Quoted delimiters: A comma inside a quoted phrase may be part of the value rather than a field boundary. For actual comma-separated files, import the file with the appropriate CSV settings instead of treating every comma as an ordinary character. The split dialog’s quote handling, when available, can also matter.
Nulls and blank strings: A null value is not necessarily the same as an empty text value. Review both cases if downstream calculations, filtering, or joins depend on the result.

When a column has inconsistent patterns, consider cleaning or standardizing it before the split, or using a more specific method such as extracting text before or after a delimiter. The best transformation is the one that expresses the intended rule and continues to work on future refreshes, not merely the one that produces attractive output for today’s sample.

Refresh behavior, names, and data types

Power Query records the split as a step in the query. On refresh, it applies that step to the current source data, which is why a split is useful for recurring imports. If source column names change, a later step that refers to the old name can fail; keep source headers stable where possible and inspect the query after structural changes.

After splitting, give the columns meaningful names instead of leaving generic names such as Location.1 and Location.2. Also check the data types. Power Query may assign or preserve a type based on earlier steps or its automatic type-detection step. A numeric-looking piece with leading zeros, such as a postal code, should usually remain text so those zeros are not lost. Set types deliberately after the split when the values’ meaning is clear.

If you load the result to a worksheet, the output table is controlled by the query. Avoid manually editing its cells to correct a split: refresh can overwrite those edits. Make the correction in Power Query or in the source data instead.

Power Query split versus Excel Text to Columns

Excel’s worksheet command is found at Data > Text to Columns. It is useful for a one-time conversion of a selected range. Power Query’s Transform > Split Column is better when the same transformation needs to be repeated during refresh, or when splitting is one of several steps in an import and cleanup process.

They also differ in where the transformation lives. Text to Columns writes the separated results into worksheet cells. Power Query stores instructions and loads a transformed result. If the source changes, Text to Columns does not automatically replay the conversion; refreshing a query does replay its recorded steps. You can still use Text to Columns for a quick worksheet task, but it is not a substitute for a refreshable query when data arrives repeatedly.

These directions describe Excel for Microsoft 365 on Windows. Excel for Mac includes Power Query features, but available connectors and interface details can vary by version; look for the Power Query Editor and its Split Column command. Excel for the web has more limited query-authoring capabilities than the desktop application, so if the split command is unavailable, open the workbook in desktop Excel.

Frequently Asked Questions

Can Power Query split text by a space?
Yes. Choose the column, then Transform > Split Column > By Delimiter, select or enter a space, and choose the split rule. If values can contain multiple spaces, preview the results and consider splitting only at the left-most or right-most space.

Can I split a column into rows instead of columns?
Yes. In the delimiter dialog, use the advanced option to split into rows if it is available in your Excel build. This is useful for delimited lists where each item should become its own record, but it increases the number of rows.

Will the split happen again when I refresh?
Yes. A split performed in Power Query is saved as a query step, so refresh repeats it on the refreshed source. The step can fail or need adjustment if the source column is renamed or its structure changes.

How do I remove spaces left after splitting?
Select the resulting text column and choose Transform > Format > Trim. Apply it to each affected column. Trim removes spaces from the ends, not spaces between words.

What if the delimiter appears inside a value?
Use a more specific delimiter, split only at the left-most or right-most occurrence, or import the source with the correct quoting rules if it is a structured CSV file. Splitting at every comma without accounting for quoted commas can divide a field incorrectly.

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.