Excel Power Query Split Column by Delimiter

How-to basics · Updated November 22, 2026

To split a column by a delimiter in Excel Power Query, select the column, go to the Home tab, click Split Column > By Delimiter, choose your delimiter (like comma, semicolon, or custom), and click OK. This transforms the data into multiple columns without altering the original source.

What Is Power Query and Why Split Columns?

Power Query is Excel’s data transformation tool, available in Excel 2016 and later (including Microsoft 365). It lets you import, clean, and reshape data before loading it into a worksheet. Splitting a column by delimiter is one of the most common tasks because raw data often arrives as a single column with values separated by commas, pipes, or other characters—for example, a full name column with “Last, First” or a list of tags like “red;blue;green”.

Unlike Excel’s Text to Columns feature, Power Query creates a repeatable query. If you refresh the data, the split is reapplied automatically, making it ideal for recurring reports.

How to Split a Column by Delimiter – Step by Step

  1. Select any cell in your data range and go to Data > From Table/Range (or From Sheet in Excel 365) to open Power Query Editor.
  2. In the Power Query Editor, click on the column header you want to split.
  3. On the Home tab, click Split Column and choose By Delimiter.
  4. In the dialog, choose a predefined delimiter (comma, semicolon, tab, etc.) or select Custom to enter your own (e.g., a pipe | or a word like ” and “).
  5. Choose how to split: Each occurrence of the delimiter (creates as many columns as needed) or Leftmost/Rightmost delimiter (splits only at the first or last instance).
  6. Click OK. The column splits into multiple columns. You can rename them by double-clicking the headers.
  7. Click Close & Load to load the transformed data back into a worksheet.

On Mac, the steps are identical, but the ribbon paths are the same (Data > From Table/Range). Excel for the web also supports Power Query, but the editor is more limited; you can still use Split Column By Delimiter in the online Power Query editor.

Choosing the Right Split Option

Power Query offers three split modes, and picking the wrong one is a common mistake:

  • Each occurrence of the delimiter – Use when you want all parts. Example: “apple,banana,cherry” becomes three columns.
  • Leftmost delimiter – Splits only at the first delimiter, leaving the rest intact. Example: “John Smith, 123 Main St” splits into “John Smith” and “123 Main St”.
  • Rightmost delimiter – Splits at the last delimiter. Example: “a-b-c” splits into “a-b” and “c”.

Also decide whether to split into columns or rows. The Split Column > By Delimiter dialog has an option to split into Rows instead of columns. This is useful when you want to normalize data—e.g., turning a cell with multiple tags into separate rows for a pivot table.

Using Custom Delimiters and Advanced Options

Sometimes the delimiter isn’t a single character. You can enter a custom string, such as a newline (#(lf)), a tab (#(tab)), or even a phrase like ” and “. For example, if a column contains “red and blue and green”, using a custom delimiter of ” and ” will split it into three columns.

You can also choose to split using a special character by clicking the “Insert special character” button in the dialog. This lets you pick from line feed, carriage return, tab, and more—useful when dealing with data copied from PDFs or web pages.

Another advanced option is to split by a delimiter that appears a variable number of times. For instance, if you have “a,,b,,,c” and you want to ignore empty values, you can use the Split Column > By Number of Characters after cleaning, but for delimiters, Power Query doesn’t ignore empties by default—you’ll need to filter out blank columns afterward.

Common Problems and How to Fix Them

Problem 1: The split creates too many columns. If your data has inconsistent delimiters (e.g., some rows have 3 commas, others have 5), you’ll get extra columns with nulls. Fix: after splitting, select the new columns, go to Home > Remove Columns to delete empty ones, or use Transform > Replace Values to handle missing data.

Problem 2: The delimiter appears inside quoted text. Power Query does not automatically respect quotes unless you specify it. In the Split Column dialog, there’s a checkbox for “Split using special characters” but not for quotes. If your data has commas inside quotes (like CSV files), use Data > From Text/CSV instead, which handles quotes automatically.

Problem 3: The split doesn’t update when source data changes. Power Query queries are refreshed manually or via a schedule. If you add new rows to the source table, you need to refresh the query (Data > Refresh All). The split will reapply to the new data.

Problem 4: You want to split but keep the original column. Power Query replaces the original column by default. If you want to keep it, duplicate the column first: right-click the column header, choose Duplicate Column, then split the duplicate.

Power Query vs. Text to Columns

Excel’s built-in Text to Columns (Data > Text to Columns) can also split by delimiter, but it works on a one-time basis and overwrites data. Power Query is superior when:

  • You need to refresh data regularly.
  • The source is external (database, web, folder).
  • You want to combine splitting with other transformations (filtering, merging, pivoting).
  • You need to split into rows, which Text to Columns cannot do.

However, Text to Columns is faster for a quick, one-off split and doesn’t require creating a query. For simple tasks, either works.

Frequently Asked Questions

Can I split by delimiter in Excel for the web? Yes, Excel for the web supports Power Query, but you must first convert your data to a table (Ctrl+T) and then use Data > From Table/Range. The Power Query editor opens in a new window with the same Split Column options.

How do I split a column into rows instead of columns? In the Split Column By Delimiter dialog, under “Split into”, choose Rows. This will create a new row for each delimiter-separated value, duplicating other columns as needed.

What if my delimiter is a line break? Use the custom delimiter option and type #(lf) for line feed or #(cr) for carriage return. You can also insert it via the special character button.

Why does my split produce blank columns? This happens when some rows have fewer delimiters. After splitting, select the new columns, go to Home > Remove Columns, and remove any that are entirely empty, or filter out nulls.

Can I split multiple columns at once? No, Power Query only splits one column at a time. You can select multiple columns, but the Split Column command will only act on the active column. You’ll need to repeat the process for each column.

Does splitting change the original data? No, Power Query does not modify the source data. It creates a query that transforms a copy. The original table remains untouched until you load the query results to a new worksheet or table.

Related

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.