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

Microsoft Excel can be a wonderful tool to use and analyze data for our daily reports. Sometimes we can get stuck with the few data changes that make our job difficult. Let’s look at one such problem here. If you have an excel sheet where all the data is entered in a single cell and needs to be split into separate columns or rows as per our requirement. There are different ways to solve this. Let’s look at the different methods available.

Method 1: Using the Text to Columns Option

Step 1 – Choose the cells where the data should be split. Go to the Data tab and choose the text to columns option in the Data Tools section.

Step 2: In the Convert Text to Column Wizard window, choose the delimited option and click next.

Step 3 – In the Delimiters section, choose Coma and click next.

Note: You can select an appropriate delimiter based on your data. You can also specify your own delimiter by selecting another one and specifying it in the text box next to it.

Step 4 – In the Column Data Format section, click the General option. Specifies the Destination cells where you want the split data to be placed. Click on End up.

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

Your data is divided into multiple columns.

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

Step 1 – Select column data, copy it.

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

Step 3: In the Paste Special window, check Transpose and click it’s fine.

Your data is split into multiple rows now.

Method 2: Using the Power Query app

power query (called get and transform Data in earlier versions of Excel) 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.

Step 2 – In the Create Table dialog box, confirm the data range. Also, select the check box My table has headers. Click on OK.

Step 3: This will open the power query Editor with selected data as shown in the following screenshot:

Step 4 – Select the cell you want to split into multiple rows. Go House tab and select split Column, choose the option by delimiter from the dropdown menu.

Step 5: In the Split Column By Delimiter window, under Select or enter delimiter select the delimiter based on your data from the dropdown menu. Go Advanced Options and choose columns rows according to your requirement. Click OK.

Note: If you have special characters as delimiters, select Personalized from the dropdown Select or enter delimiter and check Split using special characters cash register. In the center below, you can select the special character you want to use as a delimiter.

Step 6: Your data is now split into multiple columns/rows from a single cell. Now we need to go back to the original Excel application. To do that, in the Power Query editor, go to House tab and click “Close and load to…”. A data import popup will appear. In that select how and where you want to import that table and it’s fine.

Note: If you select the close and load option the data will be moved to a new sheet.

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

I hope the article was informative. Let us know in the comments which method you like the most. Thank you for reading. Happy reading!!