The Excel Power Query split function is the Split Column command in the Power Query Editor, which divides a single text column into multiple columns based on a delimiter, number of characters, or positions. To use it, load your data into Power Query (Data > From Table/Range), select the column, go to Home > Split Column, and choose a split method.
What Is Power Query and Why Split Columns?
Power Query is a data transformation tool built into Excel (and Power BI) that lets you import, clean, and reshape data before loading it into a worksheet. It is especially useful for messy data like full names, addresses, or log files where multiple pieces of information live in one column. Splitting columns is one of the most common transformations because raw data often arrives with combined fields—for example, “John Smith” or “2025-03-15 14:30:00”. Instead of using formulas like LEFT, RIGHT, or MID, Power Query gives you a visual, repeatable way to break data apart. The split is performed on a copy of the data in the Query Editor, and the original source remains untouched. Once you close and load, the result appears as a table or pivot, and you can refresh it later to apply the same split to new data.
How to Access the Split Column Feature
To split a column in Power Query, follow these steps (Excel for Microsoft 365 on Windows):
- Select any cell in your data range and go to Data > From Table/Range (or From Sheet in newer versions). If your data isn’t already a table, Excel will create one.
- In the Power Query Editor window, click on the column header you want to split.
- Go to the Home tab and click Split Column.
- Choose one of the options: By Delimiter, By Number of Characters, or By Positions.
On a Mac, the steps are identical, but the ribbon is slightly different—look for Data > From Table/Range and then the Split Column button in the Transform tab. In Excel for the web, Power Query is not available; you would need to use the desktop version.
Split by Delimiter (Most Common)
The delimiter split is what most people mean by the “split function.” It divides text at every occurrence of a specific character or string, such as a comma, semicolon, space, or pipe. When you select By Delimiter, a dialog box appears with these options:
- Select or enter delimiter: Choose from common ones (comma, semicolon, tab, space) or type a custom delimiter like ” – ” or “#” (for line breaks).
- Split at: Choose Each occurrence of the delimiter to create a new column for every delimiter, or Left-most delimiter / Right-most delimiter to split only at the first or last occurrence.
- Advanced options: You can split into rows instead of columns (useful for comma-separated lists), and you can specify whether to quote text (e.g., ignore delimiters inside quotes).
Example: If you have a column “Full Name” with values like “Alice Johnson” and you split by space, you get two columns: “Full Name.1” (Alice) and “Full Name.2” (Johnson). Power Query automatically names new columns with a suffix. You can rename them later by double-clicking the header.
Split by Number of Characters or Positions
Sometimes data is fixed-width, like a ZIP code or a date in YYYYMMDD format. For those, use By Number of Characters or By Positions.
- By Number of Characters: Splits the column after a specified count of characters. For example, splitting “ABCDEF” with a count of 3 gives “ABC” and “DEF”. You can also choose to split repeatedly—every 3 characters—which would produce “ABC”, “DEF”, and so on.
- By Positions: Lets you define multiple split points by entering character positions. For instance, to split a phone number “(555)123-4567” into area code, prefix, and line number, you could specify positions 0, 4, 7, and 12. The dialog shows a preview with vertical lines where splits will occur.
These methods are less common but essential for fixed-width files exported from legacy systems.
How to Choose the Right Split Method
Choosing the wrong method can create extra columns or lose data. Here’s a quick guide:
- If your data has a consistent separator (comma, tab, space), use By Delimiter.
- If your data has no separator but fixed-length fields, use By Number of Characters or By Positions.
- If you only need the first part of a string and want to discard the rest, use By Delimiter with Left-most delimiter and then delete the extra columns.
- If you want to turn a comma-separated list into multiple rows (unpivot-like), use Split Column > By Delimiter and choose Split into Rows in the advanced options.
Also consider whether the delimiter appears multiple times. For example, splitting “John, Smith, Jr.” by comma gives three columns. If you only want the first two, split at the left-most delimiter, then split the remaining column again if needed.
Common Problems and Misconceptions
One frequent issue is that splitting a column creates columns with generic names like “Column1.1”, “Column1.2”, which can be confusing. Always rename them right after splitting. Another problem: if your data has leading/trailing spaces, the split may produce empty columns or spaces in the results. Use Transform > Trim on the new columns to clean them. A common misconception is that the split function changes the original data—it doesn’t. Power Query creates a new transformed copy; the source remains unchanged. Also, some users expect the split to be dynamic (auto-updating when new data arrives). It is, but only if you refresh the query (Data > Refresh All). Finally, if you split by delimiter and your data contains that delimiter inside parentheses or quotes, Power Query may split incorrectly unless you enable the “Quote” option in the advanced settings, which respects quote characters.
Alternatives to Power Query Split
If you don’t want to use Power Query, Excel offers other ways to split text:
- Text to Columns (Data > Text to Columns): A wizard that splits a column into multiple columns using a delimiter or fixed width. It works directly on the sheet but is a one-time operation—it doesn’t refresh with new data.
- Flash Fill (Data > Flash Fill or Ctrl+E): Automatically fills a column based on a pattern you type. Great for quick, one-off splits, but it can be unpredictable with complex patterns.
- Formulas: Use
LEFT,RIGHT,MID,FIND, andLENto extract parts. For example, to get the first name from “Alice Johnson” in A2, you could use=LEFT(A2, FIND(" ", A2)-1). This is dynamic but requires writing formulas for each part.
Power Query is the best choice when you need a repeatable, automated process. Text to Columns is simpler for a one-time split. Flash Fill is handy for irregular patterns but not reliable for large datasets.
Frequently Asked Questions
Can I split a column into rows instead of columns?
Yes. In the Split Column by Delimiter dialog, look for the “Advanced options” section and choose “Split into Rows” instead of “Split into Columns.” This will create a new row for each delimiter-separated value, duplicating the other columns as needed. This is useful for normalizing comma-separated lists into a long format.
Why is the Split Column button greyed out?
The Split Column button is only active when you have a single column selected in the Power Query Editor. If you have multiple columns selected, or if you’re in the main Excel window (not the Query Editor), the button will be disabled. Make sure you’ve opened the Power Query Editor and clicked on the column header.
How do I split a column by multiple delimiters at once?
Power Query doesn’t have a built-in “split by multiple delimiters” option. You can split by one delimiter, then split one of the resulting columns by another delimiter. Alternatively, you can use the Text.Split function in a custom column, but that’s more advanced. For most cases, sequential splits work fine.
Does splitting affect the original data?
No. Power Query works on a preview of your data. The original source (e.g., an Excel range, CSV file, or database) is never modified. When you close and load, the split result appears as a new table or pivot. If you want to replace the original data, you would need to load the query to a new sheet and then copy it over the original—but that’s a manual step.
Can I undo a split in Power Query?
Yes. In the Power Query Editor, each step you take appears in the “Applied Steps” pane on the right. You can click the ‘X’ next to the Split Column step to remove it, or click on a previous step to see the data before the split. This is a great advantage over Text to Columns, which is harder to undo.
Is Power Query available in all Excel versions?
Power Query is available in Excel 2016 and later for Windows, and in Excel for Microsoft 365. It’s not available in Excel for the web or in Excel for Mac versions before 2019 (though it was added in Excel 2019 for Mac). If you’re using an older version, you might need to use Text to Columns instead.
Related
- Excel Import and Align with Existing Times
- Excel How to Ungroup Cells: Rows, Columns & Sheets
- How to Unmerge Cells in Excel (Group of Cells)
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- SUBSTITUTE and REPLACE in Excel: Differences ExplainedIn Excel, use SUBSTITUTE to find specified text and replace it, or use REPLACE to change characters at…
- SQL Server String Functions and How to Use ThemSQL Server string functions are built-in T-SQL tools for combining, searching, measuring, extracting, and changing text. Functions such…
- Excel Months Left in Year: Formula & GuideTo calculate the months left in the year from a date in Excel, use the formula =DATEDIF(TODAY(),DATE(YEAR(TODAY()),12,31),"m") —…
- Excel Text Left of a Character: Formulas and ExamplesTo return the text before a character in Excel, combine LEFT with FIND: if A2 contains INV-2048, enter…
- Excel LEFT Function: How to Extract Text from the StartTo return characters from the beginning of text in Excel, enter =LEFT(A2,3) in a cell, replacing A2 with…
- Formula to Capitalize Each Word in ExcelIn Excel, use the PROPER function to capitalize the first letter of each word: with the text to…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.