How to Split Columns Using Power Query in Excel

3Jul

How to Split Columns Using Power Query in Excel

Power Query makes it easy to split one column into multiple columns. This is useful when your data is stored in one field, such as full names, addresses or product codes.

Table of Contents

Why split columns in Power Query?

Splitting columns helps you clean and organize data. It makes your data easier to filter, sort, analyze and report. It is also useful when you want to separate combined values into simpler parts.

Split by delimiter

The most common way to split a column is by using a delimiter. A delimiter is a character that separates values, such as a comma, space or semicolon.

Select the column you want to split.
Go to the Home tab or Transform tab.
Click Split Column.
Choose By Delimiter.
Select the delimiter you want to use.
Click OK.

Example: Split full names

If a column contains names like John Smith, you can split it by space. This creates two columns, one for the first name and one for the last name.

If your data uses a different separator, choose that character as the delimiter. Common choices are space, comma, semicolon or tab.

Split by number of characters

You can also split a column by character count. This is useful when the data has a fixed pattern, such as codes or ID numbers.

Select the column.
Click Split Column.
Choose By Number of Characters.
Enter the number of characters for the split.
Click OK.

Split into rows

Power Query can also split a column into rows instead of columns. This works well when one cell contains multiple values separated by a delimiter.

For example, if a cell contains Apples; Oranges; Bananas, you can split the values into separate rows. This is helpful when you want each value to become its own record.

Rename the new columns

After splitting the column, Power Query creates new columns with default names. You should rename them so they are clear and easy to understand.

Change Column1.1 to a meaningful name.
Change Column1.2 to match the content.
Use simple names like First Name, Last Name, or City.

Load the result back to Excel

When you finish splitting and renaming the columns, load the transformed data back into Excel. Go to Home and click Close & Load. Excel will return the cleaned data to your worksheet.

Tips for better results

Check that all rows use the same delimiter.
Remove extra spaces before splitting if needed.
Use fixed-width splitting only when the data has a consistent pattern.
Always review the result before loading it back to Excel.

You can split by delimiter, by character count, or into rows. These tools help you turn messy data into a format that is easier to use and analyze.

Best Excel Tutorial