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

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.

You Might Want To Read This:   How to see the Violations of an Account on Instagram with the Mobile App?

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.

Min delimiter selection.

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.

Destination cell minimum

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.

You Might Want To Read This:   How to use the AuthPass password manager on Linux - Easy Steps

get special minimum

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

minimal transpose

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.

From min 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.

Create minimal table

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

Minimal Power Query Editor

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.

Min Power Query Delimiter

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.

You Might Want To Read This:   Best Windows 10 Laptops convertible to Tablet

Split column by min delimiter

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.

Minimal special character

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.

Close and load minimum

Import minimal data

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!!

You Might Want To Read This:   Microsoft publishes instructions to upgrade to Windows 11 on unsupported PCs

Leave a Reply

Your email address will not be published.