How to Split Column in Excel Power Query (5 Easy Methods)

While working with large Microsoft Excel, sometimes we need to split columns using a power query. Splitting columns by using the power query is an easy task. This is a time-saving task also. Today, in this article, we’ll learn five quick and suitable ways to split column in Excel power query effectively with appropriate illustrations.


Download Practice Workbook

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


5 Suitable Ways to Split Column Using Power Query in Excel

Let’s assume we have an Excel large worksheet that contains the information about several sales representatives of Armani Group. The name of the sales representatives, the Product Name, and the Revenue Earned by the sales representatives are given in Columns B, C, and D respectively. We can easily split a column using the Power Query Editor in Excel by using the Delimiter command, Positions command, and so on. Here’s an overview of the dataset for today’s task.

Use Delimiter Command to Split Column Using Power Query in Excel


1. Use Delimiter Command to Split Column Using Power Query in Excel

In this method, we will split a column by using the Power Query Editor with the Delimiter command. This is an easy task and time-saving also. To do that, you have to take your data in Power Query Editor first. Then you will be able to split a column using the power query. Let’s follow the instructions below to split a column using the Power Query in Excel!

Step 1:

  • First of all, select your entire data range from your dataset. We select B4:D14 and then press Ctrl + T simultaneously on your keyboard. After that, instantly a dialog box named Create Table will appear in front of you. From that dialog box, press OK.

Use Delimiter Command to Split Column Using Power Query in Excel

  • As a result, you will be able to create a table. After that, from your Data tab, go to,

Data → Get & Transform Data → From Table /Range

  • Hence, you will be taken to the Power Query Editor.

Use Delimiter Command to Split Column Using Power Query in Excel

Step 2:

  • Now, From the Power Query Editor, firstly select the column with the heading Sales Rep. After that, from the Home tab, go to,

HomeTransform → Split Column → By Delimiter

  • As a result, a Split Column by Delimiter dialog box will appear in front of you. From that dialog box, select Each occurrence of the delimiter under the Split at At last, press OK.

Use Delimiter Command to Split Column Using Power Query in Excel

  • After completing the above process you will be able to split a column named Sales Rep with the Power Query Editor which has been given in the below screenshot.

Step 3:

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

Use Delimiter Command to Split Column Using Power Query in Excel

  • Hence, a dialog box named Import Data pops up. From the Import Data dialog box, firstly, select the Table option under the Select how you want to view this data in your workbook Secondly, type =$E$4 in the Existing workbook typing box. At last, press OK.

Use Delimiter Command to Split Column Using Power Query in Excel

  • After completing the above process, you will be able to 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


2. Apply Positions Command to Split Column in Excel Power Query

Now, we will split a column by using the Power Query with the Positions command. We will split a column with the position of the text. This is an easy task and time-saving also. Let’s follow the instructions below to split a column using the Power Query Editor in Excel!

Steps:

  • First of all, select a column named Product, and then from the Home tab, go to,

HomeTransform → Split Column → By Positions

Apply Positions Command to Split Column Using Power Query in Excel

  • As a result, a Split Column by Positions dialog box will appear in front of you. From that dialog box, firstly, type 0,2 in the typing box named Positions. At last, press OK.

  • After completing the above process you will be able to split a column named Product with the Power Query Editor which has been given in the below screenshot.

Apply Positions Command to Split Column Using Power Query in Excel


3. Perform Number of Characters Command to Split Column Using Power Query

In this method, we will split a column by using the Power Query with the Number of Characters command. We will split a column with the number of characters in the text. This is an easy task and time-saving also. Let’s follow the instructions below to split a column using the Power Query Editor in Excel!

Step 1:

  • First of all, select a column named Product, and then 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

  • As a result, a Split Column by Positions dialog box will appear in front of you. From that dialog box, firstly, type 8 in the typing box named Positions. At last, press OK.

  • After completing the above process you will be able to split a column named Product with the Power Query Editor which has been given in the below screenshot.

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

Step 2:

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

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


4. Apply Uppercase to Lowercase Command to Split Column in Power Query

In this method, we will split a column by using the Power Query with the Uppercase to Lowercase command. We will split a column from the uppercase to lowercase in the text. This is an easy task and time-saving also. Let’s follow the instructions below to split a column using the Power Query Editor in Excel!

Step 1:

  • First of all, select a column named Address. Now, press right-click on your mouse. Hence, 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

  • Further, you will be able to split the column named Address by Uppercase to Lowercase.

Step 2:

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

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

  • You can also 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


5. Use Digit to Non-Digit Command to Split Column Using Power Query

Last but not the least, we will split a column by using the Power Query with the Digit to Non-Digit command. We will split a column from the digit to the non-digit in the text. This is an easy task and time-saving also. Let’s follow the instructions below to split a column using the Power Query Editor in Excel!

Step 1:

  • First of all, select a column named Address. Now, press right-click on your mouse. Hence, 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

  • Further, you will be able to 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, simply repeat Step 3 of method 1.

  • You can also split a column by the column named 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 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.


Conclusion

I hope all of the suitable methods mentioned above to split columns using power query will now provoke you to apply them in your Excel spreadsheets with more productivity. You are most welcome to feel free to comment if you have any questions or queries.

Md. Abdur Rahim Rasel

Md. Abdur Rahim Rasel

Hi! I'm Md. Abdur Rahim Rasel. Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. I have completed my graduation in Naval Architecture and Marine Engineering(NAME) from Bangladesh University of Engineering and Technology(BUET). I have a passion for learning new things with my knowledge through perseverance and hard work.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo