How to split data in one cell to multiple rows/columns in Microsoft Excel

Microsoft Excel can be a wonderful tool to use and analyze data for our day-to-day reports. At times we can get stuck with the few changes to data which hinders our work. Let us see one of such problems here. If you have an Excel sheet where all data is entered into a single cell and needs to be split into either separate columns or rows as per our requirement. There are different ways to resolve this. Let’s look at different methods available.

Method 1: Using Text to Columns Option

Step 1: Choose the cells where the data needs to be split. Go to the Data tab and choose the Text to Columns option from the Data Tools section.

 

Text To Column Option Min

 

Step 2:In Convert Text to Column Wizard window, Choose the Delimited option and click on Next.

 



Delimiter Min

 

Step 3: In the Delimiters section, choose Comma and click Next.

 

Delimiter Selection Min

 

Note: You can select an appropriate delimiter as per your data. You can also specify your own delimiter by selecting another and specifying it in the text box beside it.

Step 4:  In the Column Data Format section, click on the General option. Specify the Destination cells where you want the split data to be placed. Click on Finish.

Note: You can select any column from Data preview and change the Column data format for that particular column.

Destination Cell Min

Your data is split into multiple columns.

If you want to move data to rows follow the below steps.

Step 1: Please select the column data, copy it.

Step 2: Select the destination cell where you want to paste the data, right-click and select the Paste special option.

Paste Special Min

Step 3: In Paste Special window, check Transpose and click OK.

Transpose Min

 

Your data is split into multiple rows now.

Method 2: Using Power Query Application

Power Query (called Get & Transform Data in previous Excel versions) is available with a newer version of Microsoft Excel. You can download it from this link.

Step 1: Select the cells with the data and go to the Data tab, select From Table/Range.

 

From Table Range Min

 

Step 2: In the Create Table dialogue box, confirm the range of data. Also, select the checkbox My Table has headers. Click on OK.

 

Create Table Min

 

Step 3: This will open the Power Query Editor with the selected data as shown in the screenshot below:

 

Power Query Editor Min

 

Step 4: Select the cell which you want to split into multiple rows. Go to Home Tab and select Split Column, choose the option By Delimiter from the drop-down menu.

 

Power Query Delimiter Min

 

Step 5: In the Split Column by Delimiter window, in Select or Enter Delimiter select the delimiter as per your data from the dropdown. Go to Advanced Options and choose Columns/Rows as per your requirement. Click OK.

 

Split Column By Delimter Min

 



Note: If you have special characters as delimiters, then select Custom from the dropdown of Select or enter delimiter and check Split using special characters checkbox. In the downtown below you can select the special character you want to use as a delimiter.

Special Character Min

Step 6: Your data is now split into multiple columns/rows from a single cell. Now we need to get back to the original Excel application. To do that, in the Power Query editor go to Home Tab and click on “Close & Load To..”. Import data popup will appear. In that select how and where you want to import that table and OK.

 

Close&load Min

 

Import Data Min

 

Note: If you select the Close & Load option the data will be moved to a new sheet.

So there you have it. Your data is split into multiple rows/columns.

Hope the article was informative. Let us know in the comments which method you like best. Thank you for reading. Happy Reading!!