In Excel for Microsoft 365 on Windows, select the text column in Power Query Editor, then choose Transform > Split Column and select a method such as By Delimiter. Choose whether to split into columns or rows, set the options, and select OK; when you finish, choose Home > Close & Load to return the results to Excel.
What splitting means in Power Query
Power Query is Excel’s data import and transformation tool. Splitting separates the contents of one column according to a rule, such as a comma, a space, a fixed number of characters, or a change from lowercase to uppercase. For example, a column containing Jordan Lee can become First Name and Last Name columns when split at the space.
The key difference from editing cells directly is that Power Query records the split as a transformation step. The original source data is not rewritten by that step. Instead, the query produces transformed results that can be loaded to a worksheet or another destination. When the source changes, refreshing the query can apply the same split rule to the updated data.
This makes splitting useful for repeatable cleanup: separating names, breaking product codes into parts, extracting fields from delimited text, or turning a list of values in one cell into separate rows. The best split method depends on whether the separator is reliable and whether each record follows the same pattern.
Open the query editor and select a column
For a range already in a worksheet, select a cell in the data and choose Data > From Table/Range. If Excel asks whether the data has headers, confirm the appropriate setting and create the query. For an external source, use the relevant command under Data > Get Data, connect to the source, and choose Transform Data to open the editor.
In Power Query Editor, click the header of the column to split. You can select more than one column by holding Ctrl while selecting headers, but it is usually clearer to split and check one column at a time. Then use Transform > Split Column. The same split commands may also be available from the column header’s right-click menu. The Home tab can contain related transformation controls, but the Transform tab is the most direct route for the split commands.
After choosing a split method, inspect the preview before accepting it. Power Query may create new columns with names such as Column1. You can rename them by double-clicking a header or using the rename command. When the result is ready, choose Home > Close & Load. To control where the result goes, use the Close & Load drop-down and choose Close & Load To, where available.
Split by a delimiter
A delimiter is a character or text string that marks a boundary. Common delimiters include a comma, semicolon, slash, hyphen, tab, or space. Select Transform > Split Column > By Delimiter, choose a listed delimiter or enter a custom one, and review the advanced options in the dialog.
For example, suppose a column contains North-451-Blue and the hyphen separates region, item number, and color. Splitting at each occurrence of the hyphen produces three columns: North, 451, and Blue. A semicolon-delimited field such as red;green;blue can likewise become three values. If the delimiter is more than one character, enter the complete string rather than assuming Power Query will treat several separate characters as one delimiter.
The dialog’s split setting determines how many boundaries are used:
- Each occurrence of the delimiter splits at every match. Use this when each separator marks a field boundary.
- Left-most delimiter splits only at the first match. This is useful for separating a prefix from the remaining text.
- Right-most delimiter splits only at the last match. For example, it can separate a file name from an extension when earlier periods may occur in the name.
Check how the preview handles empty values and repeated delimiters. If a source value contains adjacent delimiters, the resulting output may include a blank field between them. That blank can be meaningful—for instance, an omitted middle value—or it can reveal inconsistent source formatting that needs a separate cleanup step.
Choose whether to split into columns or rows
Splitting into columns is appropriate when the separated values describe different fields in the same record. A full name may become two columns, or a code may be divided into category and identifier. The result remains one row for each original record, although a split into columns can create additional columns for the values found.
Splitting into rows is better when one cell contains a list of multiple items that should each be treated as a separate record. In the delimiter dialog, open Advanced options and choose to split into rows rather than columns, if that option is available for the selected split. For example, a cell containing apples;pears;plums can become three rows, with one fruit in each row. Other columns in the original record are repeated alongside the split values, which helps preserve context.
Use rows when the values need to be filtered, counted, matched, or summarized individually. Use columns when each segment has a distinct meaning and belongs beside the other fields in one record. Splitting into rows changes the number of records, so check that later totals or grouping steps are intended to work at that more detailed level.
Other ways to split a column
A delimiter is not always the right rule. Under Transform > Split Column, Power Query also provides methods for patterns that are based on position or character type. The exact choices shown can depend on the method and Excel build, but commonly include:
- By Number of Characters: Divide a fixed-width value after a specified number of characters. This suits consistently formatted codes, such as a three-character prefix followed by a remaining identifier. Check that all source values follow the same width.
- By Positions: Split at specified character positions. This is useful for fixed-layout text where fields always occupy the same positions, even if there is no visible separator.
- By Lowercase to Uppercase or Uppercase to Lowercase: Separate text where a change in capitalization marks a boundary, such as a lowercase word immediately followed by a capitalized word.
- By Digit to Non-Digit or Non-Digit to Digit: Separate a letter-based prefix from a numeric portion, or the reverse, when the boundary is the transition between digits and other characters.
These pattern-based methods can save time, but they rely on consistency. A fixed-position split may misread a longer code, while a letter-to-digit split may not handle punctuation or a value with multiple transitions as expected. Preview several representative records—including the shortest, longest, and least typical values—before applying the transformation.
Handle multiple delimiters and irregular data
Some columns use more than one separator. A field might contain R-18/Green, where a hyphen separates the prefix and number while a slash separates the number and color. One approach is to split at the first delimiter, then select the remaining column and split it again using the second delimiter. Each operation appears as a separate applied step, making the sequence easier to inspect and adjust.
If several characters should all count as separators, check the delimiter dialog for an option to treat consecutive delimiters as one, when available. Otherwise, splitting on each delimiter can produce empty columns where separators repeat. You can also replace or normalize separators in an earlier transformation, then split on the single standardized character. Avoid replacing characters that may be legitimate content in some records.
Trim unwanted spaces after splitting if values have spaces around the boundaries. Select the affected text columns and use Transform > Format > Trim. This removes leading and trailing spaces; it does not remove spaces between words. Also check the data type icons in the column headers. Power Query may assign a type such as number or date, and converting an identifier like 00427 to a number can remove its leading zeros. Set identifiers to text when those zeros are part of the value.
Refresh behavior and loading the result
A split is stored as an applied step in the query, usually with a name describing the operation. You can review the sequence in the Query Settings pane, change a step’s settings where offered, or remove a step. The order matters: splitting after a replacement or trimming step can produce a different result from splitting first. Review the steps when a query has several transformations.
After loading, refresh the query from Excel using Data > Refresh All, or refresh the relevant query from its context menu in the Queries & Connections pane. The split runs again on the source data available to the query. If new records use a different format, the transformation may create unexpected blanks or errors; refresh does not automatically infer a new rule that fixes inconsistent data. Check the refreshed output when the source structure changes.
Power Query output is not the same as a live formula that recalculates in each cell as you type. The query result is updated when the query refreshes, subject to the source and connection settings. If you need a one-time split for a small, static range, Excel’s worksheet Text to Columns command may be quicker. If you need repeatable import and cleanup, Power Query is often more suitable.
Common problems and how to fix them
- The split creates too many columns: The delimiter may occur inside values, or the dialog may be set to split at every occurrence. Try splitting only at the left-most or right-most match, or clean the source before splitting.
- Some values are blank after splitting: Check for missing fields, repeated delimiters, or records that do not follow the same pattern. Do not automatically remove blanks if they represent meaningful missing data.
- Text looks like a date or number: Review the detected data type after splitting. Choose Text for codes and identifiers where formatting, leading zeros, or exact characters must be preserved.
- Spaces remain around the pieces: Apply Trim to the resulting text columns. If the unwanted character is not an ordinary space, identify it first; trimming spaces will not remove every kind of whitespace or punctuation.
- New source rows are not included: Confirm that the query source covers the new data. For worksheet data, converting the source range to an Excel table before creating the query can make the source easier to extend; refresh afterward.
- The output is different after refresh: Compare the new source records with the records used to define the split. A delimiter-based rule only works consistently when the input pattern is consistent.
Excel desktop, Mac, and web differences
The steps above describe Excel for Microsoft 365 on Windows. Excel for Microsoft 365 on Mac includes Power Query features, but the available data sources, editor controls, and ribbon layout can differ by version. If a command is not in the same place, open the query editor from the Data tab and look for the split controls on its Transform tab. The exact set of features may not match Windows.
Excel for the web does not provide the same full query-authoring experience as Excel desktop. Depending on the workbook and connection, web features may allow interaction with or refresh of queries, but use the desktop editor when you need to create or adjust a split transformation and the relevant controls are unavailable online. A workbook created on desktop can still be shared, but verify that its data connections and refresh behavior suit the people who will use it.
Frequently Asked Questions
How do I split a cell by a comma in Power Query?
Select the column in Power Query Editor and choose Transform > Split Column > By Delimiter. Select comma, choose whether to split at each comma or only the first or last one, then select whether the result should be columns or rows.
Can Power Query split text into rows instead of columns?
Yes. In the delimiter split dialog, open Advanced options and choose the rows option when it is offered. This creates a separate row for each split value and repeats the other fields from the original record.
Does splitting in Power Query change my original data?
No. The split is a query transformation, and the results are loaded separately from the source data. The query stores the rule so it can be run again when refreshed.
How do I split only at the first delimiter?
Choose By Delimiter, then select the left-most delimiter option in the split settings. This creates two pieces at the first matching delimiter and leaves later matches in the remaining text.
Why did my leading zeros disappear after splitting?
The resulting column may have been assigned a numeric data type. Change its type to Text in Power Query so values such as 00427 remain identifiers rather than being treated as numbers.
Can I split a column without a delimiter?
Yes. Choose a method such as By Number of Characters, By Positions, or a character-transition option if the data follows that pattern. Review varied examples carefully because these methods depend on consistent formatting.
Related
- How to Record a PowerPoint Presentation in Zoom
- Parts Inventory Spreadsheet: What to Track and How
- Online Expense Tracking Spreadsheet: How to Set It Up
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Transfers in Google Sheets: Move, Copy, and Import DataGoogle Sheets can transfer spreadsheet data by moving or copying cells, copying a whole sheet, or importing a…
- How to Search in Google SheetsTo search in Google Sheets, press Ctrl+F on Windows or Command+F on Mac, enter a word or number,…
- Excel Sum Table Column: How to Total Values CorrectlyTo sum a column in an Excel table, use =SUM(Table1[Sales]), replacing Table1 with your table name and Sales…
- Microsoft Excel Print Area: Set, Change, and ClearIn Microsoft Excel for Microsoft 365 on Windows, select the cells you want to print, then choose Page…
- Can You Sort Google Sheets by Color? Steps and WorkaroundsYes. In Google Sheets in a web browser, you can sort rows by a cell’s fill color or…
- Excel Not Equal to Null? How to Check Blank CellsExcel worksheet formulas do not have a special NULL value: to test whether a cell is not blank,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.