Split Excel Columns with Power Query: 5 Proven, Easy Methods

Method 1 – Using Delimiter Command to Split Column in Excel Using Power Query

Step 1:

• Select your entire data range from your dataset. Select B4:D14 and press Ctrl + T simultaneously on your keyboard. A dialog box named Create Table will appear. Press OK.

• Create a table. From your Data tab, go to,

Data → Get & Transform Data → From Table /Range

• You will be taken to the Power Query Editor.

Step 2:

• From the Power Query Editor, select the column with the heading Sales Rep. From the Home tab, go to,

Home → Transform → Split Column → By Delimiter

• A Split Column by Delimiter dialog box will appear. Select Each occurrence of the delimiter under the Split. Press OK.

• Split a Sales Rep column with the Power Query Editor, as shown in the screenshot below.

Step 3:

• Select Close & Load → Close & Load To, as shown in the following picture.

• A dialog box named Import Data pops up. Select the Table option from the Import Data dialog box under the Select how you want to view this data in your workbook. Type =$E$4 in the Existing workbook typing box. Press OK.

• Import data in your Excel sheet from the Power Query Editor, as shown in the screenshot below.

Method 2 – Applying Positions Command to Split Column with Excel Power Query

Steps:

• Select a column named Product, and from the Home tab, go to,

Home → Transform → Split Column → By Positions

• A Split Column by Positions dialog box will appear in front of you. From that dialog box, type 0,2 in the typing box named Positions. Press OK.

• Split a column named Product with the Power Query Editor, as shown in the screenshot below.

Method 3 – Performing Number of Characters Command to Split Column Using Power Query

Step 1:

• Select a column named Product, and from the Home tab, go to,

Home → Transform → Split Column → By Number of Characters

• A Split Column by Positions dialog box will appear in front of you. From that dialog box, type 8 in the typing box named Positions. Press OK.

• Split a column named Product with the Power Query Editor, as given in the screenshot below.

Step 2:

• To import the split column in the Excel sheet, repeat Step 3 of method 1.

Method 4 – Applying Uppercase to Lowercase Command to Split Column in Power Query

Step 1:

• Select a column named Address. Press right-click on your mouse. A window pops up. From that window, go to,

Split Column → By Uppercase to Lowercase

• Split the column named Address from Uppercase to Lowercase.

Step 2:

• To import the split column in the Excel sheet, repeat Step 3 of method 1.

• Split a column by the column named Address from Lowercase to Uppercase.

Method 5 – Using Digit to Non-Digit Command to Split Column Using Power Query

Step 1:

• Select a column named Address. Press right-click on your mouse. A window pops up. From that window, go to,

Split Column → By Digit to Non-Digit

• You can split the column named Address by Digit to Non-digit.

Step 2:

• To import the split column in the Excel sheet, repeat Step 3 of method 1.

• Split a column by Address by Non-digit to Digit.

Things to Remember

➜ While a value can not be found in the referenced cell, the #N/A error happens in Excel.

➜ You can press Ctrl + T simultaneously on your keyboard to create a table.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Related Articles

• Compare Two Tables with Power Query in Excel
• Dealing with Tables with Changing Headers in Power Query

<< Go Back to Power Query Excel | Learn Excel