How to convert text to rows in Excel
By
SpreadCheaters
In today’s tutorial we’ll learn how to convert or split the text data into rows. Let’s look at the dataset given above. It contains the names and email addresses of people all written in one cell of the first row and there is another cell in the second row which contains the similar data but only in the first cell as shown above.
We’ll have to use Excel’s new tool Power Query to accomplish this task. So, follow along the steps mentioned below to learn how to convert this data into separate rows.
Excel is a very handy software tool for organizing and manipulating any type of datasets. It has many functions for calculations and data visualization. It also provides us with a lot of functions for re-arranging datasets. There is a built-in function in Excel which helps us to convert or split the data into columns. However, if we want to split or convert the text data available in one cell to rows then there are no direct functions or tools available.
Step 1 – Import the Data from Table/Range
– Click on the cell that currently has the data. In our case it is A1.
– Locate the Data tab on the list of main tabs.
– Go to the Get & Transform Data group and click on From Table/Range.
– This will open up a small dialog box asking you to choose a range of data to create an Excel table.
– Our data range will automatically be selected, however, if Excel selects the wrong data range then we can change the selection in this step.
– Since our data doesn’t have the headers, we’ll keep “my data has headers” unchecked.
– Pressing the OK button on this dialog box will open up the Power Query Editor as shown above.
Step 2 – Locate the Split Columns option on Home Tab
– In Power Query Editor, locate the Split Columns option in Home Tab.
– Click on the dropdown menu icon and select By Delimiter as shown above.
Step 3 – Choose appropriate parameters to split data to rows
– The last step will open up a new dialog box Split Column by Delimiter as shown above.
– Choose appropriate delimiter as per your dataset. We have the data separated by semicolon, so, we’ll choose the Semicolon as a delimiter.
– Click on Advanced Options and check the radio button against Rows in Split into options.
Step 4 – Convert Text Data to Rows
– Pressing the OK button will transform and convert the text data into rows only.
– Then press the “Close & Load” action button to load the data back to the Excel file as shown above. Your data is now converted to rows by Power Query.
