How to Split Column in Excel Power Query: 5 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.

Use Delimiter Command to Split Column Using Power Query in Excel

  • 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.

Use Delimiter Command to Split Column Using Power Query in Excel

Step 2:

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

HomeTransform → Split Column → By Delimiter

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

Use Delimiter Command to Split Column Using Power Query in Excel

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

Step 3:

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

Use Delimiter Command to Split Column Using Power Query in Excel

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

Use Delimiter Command to Split Column Using Power Query in Excel

  • Import data in your Excel sheet from the Power Query Editor, which has been given in the below screenshot.

Use Delimiter Command to Split Column Using Power Query in Excel


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,

HomeTransform → Split Column → By Positions

Apply Positions Command to Split Column Using Power Query in Excel

  • 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.

Apply Positions Command to Split Column Using Power Query in Excel


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,

HomeTransform → Split Column → By Number of Characters

Perform Number of Characters Command to Split Column Using Power Query in Excel

  • 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.

Perform Number of Characters Command to Split Column Using Power Query in Excel

Step 2:

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

Perform Number of Characters Command to Split Column Using Power Query in Excel


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

Apply Uppercase to Lowercase Command to Split Column Using Power Query in Excel

  • 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.

Apply Uppercase to Lowercase Command to Split Column Using Power Query in Excel

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

Apply Uppercase to Lowercase Command to Split Column Using Power Query in Excel


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

Use Digit to Non-Digit Command to Split Column Using Power Query in Excel

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

Use Digit to Non-Digit Command to Split Column Using Power Query in Excel

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.

Use Digit to Non-Digit Command to Split Column Using Power Query in Excel


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


<< Go Back to Power Query Examples | Power Query Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Md. Abdur Rahim Rasel
Md. Abdur Rahim Rasel

MD. ABDUR RAHIM is a marine engineer proficient in Excel and passionate about programming with VBA. He views programming as an efficient means to save time while managing data, handling files, and engaging with the internet. His interests extend to Rhino3D, Maxsurf C++, AutoCAD, Deep Neural Networks, and Machine Learning, reflecting his versatile skill set. He earned a B.Sc in Naval Architecture & Marine Engineering from BUET, and now he has become a content developer, creating technical content... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo