To split data in Excel Power Query, select the column you want to split, go to the Home tab, click Split Column, and choose By Delimiter, By Number of Characters, or By Positions. This transforms your data without altering the original source, and you can load the results into a worksheet or keep the query for refreshable analysis.
What Power Query Splitting Actually Does
Power Query is Excel’s built-in data transformation tool, available in Excel 2016 and later (including Microsoft 365). When you split a column, you are not editing the raw data—you are creating a series of steps that define how the data should be broken apart. The original file or table remains untouched, which is a key advantage over using Excel’s Text to Columns wizard. Every split operation is recorded in the Applied Steps pane on the right side of the Power Query Editor, so you can revisit, reorder, or undo any step later. This makes splitting reversible and repeatable: if your source data updates, refreshing the query re-runs the split automatically.
How to Access Power Query and Split a Column
To start, select your data range and go to Data > From Table/Range (or Data > From Sheet in newer versions). This opens the Power Query Editor. If your data is in a named Excel table, you can also right-click the table and choose Get Data from Table/Range. Once in the editor:
- Click on the column header you want to split.
- Go to the Home tab, find the Split Column dropdown.
- Choose one of the options: By Delimiter, By Number of Characters, or By Positions.
- Configure the settings in the dialog box that appears, then click OK.
- Click Close & Load to send the split data back to a worksheet, or Close & Load To to choose the destination.
On a Mac, the ribbon paths are identical for Microsoft 365, but the dialog boxes may look slightly different. Excel for the web does not support Power Query; you would need to use the desktop app for this feature.
Choosing the Right Split Method
Each split method solves a different problem. Here is how to decide:
- By Delimiter – Use when your data has a consistent separator like a comma, semicolon, tab, or custom character. For example, splitting “John Doe, Sales Manager” into two columns using a comma. You can also split at each occurrence or only the first/last occurrence.
- By Number of Characters – Use when each value has a fixed length, such as splitting “ABC12345” into “ABC” and “12345” by choosing 3 characters for the first column. This is common with codes or IDs.
- By Positions – Use when you want to split at specific character positions, like breaking a date string “20240115” into year (positions 1-4), month (5-6), and day (7-8). This method lets you define multiple breakpoints at once.
For delimiters, you can choose to split at the leftmost, rightmost, or every occurrence. The Advanced options in the dialog let you specify whether to split into columns or rows—splitting into rows is useful for normalizing denormalized data.
Common Problems and How to Fix Them
One frequent issue is that Power Query treats a column as text when it contains mixed types, so numbers may not split correctly. Fix this by setting the column data type to Text before splitting (right-click column > Change Type > Text). Another problem: your delimiter appears inconsistently, like a comma sometimes followed by a space. Use the Custom delimiter option and enter “, ” (comma plus space) to handle that. If you accidentally split too many columns, simply delete the extra steps in the Applied Steps pane by clicking the ‘x’ next to the split step. Also, if your data has leading/trailing spaces after splitting, use Transform > Format > Trim on each new column.
Power Query Split vs. Excel Text to Columns
Excel’s classic Text to Columns (Data > Text to Columns) works on the worksheet and is fine for one-off tasks. However, it overwrites the original data unless you copy it first, and it does not refresh when the source changes. Power Query splitting is superior for recurring data because it stores the transformation logic. For example, if you import a weekly CSV file, you set up the split once, and every refresh applies the same split automatically. Text to Columns also cannot split into rows, while Power Query can. The only advantage of Text to Columns is that it works on data that is not in a table and does not require loading into Power Query, which may be faster for a tiny, one-time cleanup.
Cost and Availability
Power Query is included with all desktop versions of Excel from 2016 onward, including Microsoft 365 subscriptions. There is no extra cost. However, it is not available in Excel for the web or in the free mobile apps. If you are using an older version like Excel 2013, you would need to install the Power Query add-in separately (it was free but is no longer supported). For most users on Microsoft 365, the feature is already there under the Data tab.
Safety and Data Integrity
Because Power Query does not modify your original data, it is inherently safe. Still, before you load the split results, consider these points: always keep a backup of your source file if it is a CSV or external database. When you load to a worksheet, the new columns appear to the right of the existing table, so ensure you have enough empty columns to avoid overwriting data. If you are splitting a column that contains formulas, those formulas are not carried over—Power Query treats values as data, not formulas. Also, if your data contains sensitive information, be aware that the query steps are stored in the workbook; anyone with access to the file can view the transformation steps in the Power Query Editor.
Alternatives to Power Query for Splitting
If you cannot use Power Query, a few alternatives exist. The Text to Columns wizard works for simple delimiters. You can also use Excel formulas like =TEXTSPLIT(A1,",") in Microsoft 365 (this spills results into adjacent cells). For fixed-width splits, =LEFT(), =MID(), and =RIGHT() work well. Another option is using Flash Fill (Data > Flash Fill, or Ctrl+E) to manually type the first split result and let Excel detect the pattern—this is quick but not reproducible. For advanced users, VBA can split strings with the Split() function, but that requires coding and is not refreshable.
Frequently Asked Questions
How do I split a column by comma in Power Query?
Select the column, go to Home > Split Column > By Delimiter, choose Comma from the dropdown, and click OK. You can choose to split at each comma or only the first one.
Can I split a column into rows instead of columns?
Yes. In the Split Column dialog, under Advanced options, select Split into Rows. This creates one row for each part of the split value, duplicating the other columns in that row.
Why is my split button greyed out?
The Split Column button is only active when you have selected a single column in the Power Query Editor. If you have multiple columns selected, click on one column header only, then try again.
Does Power Query split work on merged cells?
No. Merged cells are not supported in Power Query. You must unmerge the cells before loading the data into the editor, or use a different method like Text to Columns.
Can I split by multiple delimiters at once?
Not directly in one step. You can add multiple split steps sequentially in the Applied Steps pane. For example, first split by comma, then split one of the new columns by semicolon.
Will the split update if my source file changes?
Yes, if you load the data from a file or table and then refresh the query (Data > Refresh All), the split steps re-run on the new data. This is the main benefit over manual splitting.
Related
- Free Excel Inventory Tracking Sheet Template? Yes—Here’s How
- Excel Inventory Tracker Template: How to Build One
- Excel Insert Yes No Checkbox: 2 Ways
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Bar Names: What Each Part of the Window DoesIn Excel for Microsoft 365 on Windows, the main interface areas people call “bars” include the title bar,…
- Excel Solver: How to Save and Reuse Solver ScenariosYes. After Solver finds a solution in Excel for Microsoft 365 on Windows, choose Save Scenario in the…
- Google Sheets Dropdown Formula: How It Really WorksGoogle Sheets does not create a dropdown with a standalone cell formula: use Data validation to make the…
- Create an Email Signature in Outlook: Step-by-StepTo create an email signature in Outlook, open the signature settings for your version, choose New, enter and…
- How to Mass Archive Emails in OutlookTo mass archive emails in Outlook, open the folder containing the messages, select the messages you want, and…
- Excel Power Query Join Types and When to Use EachExcel Power Query has six join types for merging tables: Left Outer, Right Outer, Full Outer, Inner, Left…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.