In Excel for Microsoft 365 on Windows, split a column in Power Query by opening the query, selecting the column, and choosing Transform > Split Column > By Delimiter. Choose the delimiter and whether to create columns or rows, then select Home > Close & Load to return the transformed data to Excel.
What splitting a column does
Power Query is Excel’s data-import and transformation tool. Splitting separates text stored in one column into multiple pieces according to a rule, such as a comma, a space, a fixed character position, or a change from letters to digits. The result becomes part of the query’s repeatable transformation steps rather than a one-time edit to the worksheet.
For example, a source column might contain Jordan Lee, West, 2026. Splitting at each comma can produce separate fields for the name, region, and year. Alternatively, a column containing Excel;Power BI;SQL might be split into separate rows so each skill has its own record. The right result depends on how you need to filter, summarize, or join the data later.
Power Query does not alter the original source file or source table when you apply a split. It creates a transformed output that you can load to a worksheet or, in some workflows, the Data Model. When the source changes, refreshing the query reapplies its saved steps to the newer data.
Split a column by delimiter
Use a delimiter when a consistent character or text string marks the boundary between values. Common delimiters include commas, semicolons, pipes, tabs, slashes, and spaces. First, make sure the source data is available to Power Query. For a worksheet range, select a cell in the data and choose Data > From Table/Range. If Excel asks whether to create a table, confirm the range and headers. Existing Excel tables can also be imported from this command.
- In Power Query Editor, select the column you want to divide. Selecting the column header helps ensure the operation applies to the intended field.
- Choose Transform > Split Column > By Delimiter.
- In the dialog, choose a listed delimiter or enter a custom one. For a tab or an unusual separator, use the custom option and enter the actual character or text.
- Choose how to split: at each occurrence, at the left-most occurrence, or at the right-most occurrence. The available choices depend on the selected split settings.
- Open Advanced options if you need to choose whether the result is split into columns or rows. Review the preview before confirming.
- Choose OK, inspect the result, and then select Home > Close & Load.
Suppose a column contains INV-4821-CA. Splitting at every hyphen creates three pieces: INV, 4821, and CA. If the format is always a prefix followed by a variable code and a region, splitting at every hyphen may be appropriate. If you only need the final region, splitting at the right-most hyphen avoids creating unnecessary fields.
A custom delimiter can contain more than one character, which is useful for strings such as :: or / . Match spaces around the separator when they are part of the source text; otherwise, the resulting pieces may retain leading or trailing spaces. Power Query also has a Trim transformation if you need to remove spaces from the results.
Choose rows or columns
Splitting into columns is best when each part describes a different attribute of the same record. A full name divided into first and last name, or a product code divided into category and size, often belongs in separate columns. Each original row stays one row, while additional fields appear beside it.
Splitting into rows is useful when one cell contains a list of repeated values. If a customer row has North;South;Central in a Territories field, splitting at semicolons into rows creates one territory value per output row. Other fields from that source record are repeated on the resulting rows, which keeps each territory associated with the customer.
Choose rows when the pieces are multiple instances of the same kind of information, not distinct attributes. This structure is usually easier to filter, count, group, and relate to other tables. Choose columns when the pieces have different meanings and should be used as separate fields. A common mistake is to split a list into columns just because the dialog makes that option prominent; doing so can produce a very wide table and awkward analysis when the number of list items varies.
Before choosing, ask what one output row should represent. If it should represent one customer, split attributes into columns. If it should represent one customer-territory combination, split the territory list into rows. That simple check often resolves the decision.
Other ways to split text
Delimiter splitting is the most common method, but the Split Column menu includes other rules. The exact choices shown can depend on the type of data and the Excel build. Useful alternatives include:
- By number of characters: divide text after a fixed number of characters. This suits codes with a stable format, such as a two-character country prefix followed by a fixed-length identifier.
- By positions: specify character positions where the text should be divided. This is helpful for fixed-width data, such as a file where characters 1–8 represent a date and later positions represent an account code.
- By lowercase to uppercase or uppercase to lowercase: use changes in letter case as boundaries, such as separating
orderDateintoorderandDate. - By digit to non-digit or non-digit to digit: separate text and numeric runs in a mixed code such as
AB123XY.
These rules work best when the source follows a consistent pattern. A position-based split can be wrong if records contain different lengths, while a case-change split may divide abbreviations unexpectedly. Preview several representative rows, including unusual values, before applying a rule to the full dataset.
Check the result and fix common problems
A successful split depends on the source format being consistent enough for the chosen rule. Check the preview for short values, long values, blanks, and records that do not contain the expected delimiter. If only some rows contain a comma, a split at each comma will not magically infer missing information. The resulting columns may have blank values where a piece is absent.
- Unexpected extra columns: the delimiter may occur inside a value as well as between fields. Consider splitting only at the left-most or right-most occurrence, or choose a more specific delimiter.
- Values remain joined: verify that the selected delimiter exactly matches the source. A regular space, a nonbreaking space, and a tab are different characters.
- Blank pieces appear: consecutive delimiters or a delimiter at the start or end can create empty segments. Decide whether those represent meaningful missing fields or should be cleaned up.
- Numbers or dates look different: splitting creates text values, and Power Query may assign or retain data types in later steps. Check the column type icons and set the correct type after the split when needed.
- Rows multiply unexpectedly: splitting into rows deliberately creates one output row for each piece. Confirm that this is intended before loading or calculating totals.
Pay particular attention to column names. Power Query may give new columns generic names such as the original name with a numeric suffix. Rename them to descriptive labels, such as Area Code and Phone Number, so downstream formulas and reports are easier to understand. If another step refers to the original column name, renaming or deleting columns can affect that step; review the Applied Steps pane if an error appears.
Refresh, load, and maintain the query
When you select Home > Close & Load, Excel loads the query result using the current load settings. In many cases, the output appears as a table on a worksheet. The source table and the query output are separate: editing a loaded result cell is not a reliable way to change the transformation, because a refresh can replace that output with the query result again.
To update results after the source changes, use Data > Refresh All or right-click the query output and choose Refresh. Power Query runs the saved steps again, including the split. This makes it useful for recurring reports: a monthly file can follow the same process as long as its columns and text patterns remain compatible. If a new file changes its delimiter or column layout, the query may need adjustment.
Keep the transformation steps in a sensible order. For example, remove irrelevant columns, split the needed field, rename the new columns, and set their data types. Renaming columns early can make later steps easier to read, but avoid changing a source column name without checking later steps that depend on it. The Applied Steps list records the operations and lets you select a step to inspect its result.
Power Query compared with formulas
Power Query is a strong choice when you repeatedly import or reshape data, especially when the same operation must be applied to many rows or refreshed from a changing source. A worksheet formula can be convenient for a quick result in a live grid, but it can require fill-downs, formula maintenance, or newer functions that may not be available in every Excel version.
For a one-off split, Excel’s worksheet command Data > Text to Columns can also separate a selected range. Unlike a query transformation, it changes the selected worksheet cells directly and is not automatically replayed as a query during refresh. Power Query is generally preferable when you need a documented, repeatable import process; Text to Columns can be faster for a small manual cleanup.
Neither approach fixes ambiguous data. If a comma appears both as a separator and inside a quoted address, blindly splitting at every comma can produce incorrect fields. Check how the source encodes its values and whether it is a properly structured delimited file. A delimiter-based split is a transformation rule, not a substitute for understanding the source format.
Excel for Mac and the web
The steps above are written for Excel for Microsoft 365 on Windows. Excel for Mac includes Power Query capabilities, but available data sources and editor features can depend on the Excel build. If the Power Query Editor is available, look for the Split Column command on its Transform tab; ribbon placement and dialog details can vary. Excel for the web also has Power Query capabilities in supported Microsoft 365 environments, but not every connector or authoring feature is available in every account. If a command is missing, check the current app and subscription support rather than assuming the query itself is invalid.
Frequently Asked Questions
Can Power Query split one column into multiple rows?
Yes. In the By Delimiter dialog, use Advanced options to select Rows instead of Columns. Each separated value becomes a row, and the other fields from its original record are repeated.
How do I split only at the first or last delimiter?
Select the source column and open Transform > Split Column > By Delimiter. In the split settings, choose the left-most or right-most delimiter rather than splitting at each occurrence.
Does splitting in Power Query change my original data?
No. The query transforms data for its output; it does not rewrite the original source table or file. The loaded result can be refreshed from the source, and refresh reapplies the saved split step.
Can Power Query split by a space or a custom character?
Yes. Choose Space from the delimiter list when appropriate, or enter a custom delimiter in the By Delimiter dialog. Remember that tabs and different kinds of spaces are not interchangeable.
Why does my split produce blank values or extra columns?
Blank pieces can result from missing delimiters, repeated delimiters, or delimiters at the edges of a value. Extra columns can appear when the chosen separator occurs more often than expected. Inspect those records and choose a more precise split rule or clean the source first.
Related
- Data Validation and Quality Assurance in Excel
- Power Query Remove Leading Zeros Without Losing IDs
- Power Query Reference: What It Does and When to Use It
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Can ChatGPT Compare Two Excel Sheets?Yes. ChatGPT can compare two Excel sheets when you upload the workbook or files in a chat with…
- How to Make a Block Schedule in Google Sheets Step by StepTo make a block schedule in Google Sheets, set up a grid with days across the top and…
- Highlight Duplicates in Excel: A Step-by-Step GuideTo highlight duplicate values in Excel for Microsoft 365 on Windows, select the cells to check, then choose…
- Excel Time Format: Show Hours and Minutes Only CorrectlyTo show hours and minutes in Excel, select the cells, press Ctrl+1, choose Custom, and enter h:mm (or…
- How to Insert a Check Box in Word 365 and Make It ClickableTo insert a clickable check box in Word 365, turn on the Developer tab, then choose Developer >…
- How to Double Space in Word: Steps, Tips, and FixesTo double-space text in Word, select it and choose Home > Line and Paragraph Spacing > 2.0. To…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.