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.
Step 2:In Convert Text to Column Wizard window, Choose the Delimited option and click on Next.
Step 3: In the Delimiters section, choose Comma and click Next.
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.
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.
Step 3: In Paste Special window, check Transpose and click OK.
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.
Step 2: In the Create Table dialogue box, confirm the range of data. Also, select the checkbox My Table has headers. Click on OK.
Step 3: This will open the Power Query Editor with the selected data as shown in the screenshot below:
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.
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.
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.
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.
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!!