How to convert text to rows in Excel?

For analysis and report preparation, we occasionally need to extract data from a variety of files in a variety of formats. In such cases, we might need to convert portion of the cell's data to columns. There are several techniques in Excel to transform text to rows of data.

Split a Text Cell into Two or More Rows in Excel

With Excel's Text to Columns and Transpose Data functions, text in a single cell that is delimited by a certain character (such as a space, tab, semicolon, etc.) can be divided into numerous rows. Let's say you wish to divide the semicolon-delimited product list in the image below into rows.

1.   First, use a delimiter to divide the cell's values into columns. Go to the Ribbon, choose a text cell (B1), and then select Data > Text to Columns.

2. Click Next after leaving the default file type (Delimited) in Step 1 of the Text to Columns Wizard.

3.  Click Next after selecting Semicolon under Delimiters. You can see how this data will be delimited in the Data preview. Tab, comma, space, and user-defined delimiters are additional delimiters that could be used.

4. Leave the default data format (General) and click Finish to complete the process. Other data kinds, such text or date, are also available for selection here. In the Data preview, you may also pick out certain columns and modify their data types. Leave the first cell blank to split data for the Destination.

This section causes the text in cell B1 to be divided into Columns B through F at each semicolon.

5. Now move the numbers from Row 1 to Column B using the Transpose Data tool. Transpose cells C1:F1 starting in cell B1 (such that the current value in cell B1 remains there). To do this, choose and right-click the cells you want to transpose (C1:F1), then select Copy (or use CTRL + C on the keyboard).

6.  Choose Transpose from the Paste Options menu when you right-click the starting cell for the transposed data (B5).

In Columns B-D, cells B5 to D9, values from cells B1 to B3 have now been reversed. Cells' initial data can be removed.

Static method using Paste special

In this approach, Excel's Paste Special Transpose tool is used to transpose the data. The transposed data in this case is static. It stays the same while the underlying data changes. Using Excel's Paste Special Transpose tool, we may transpose the selected data.

Let's rapidly comprehend with the aid of a straightforward example.

We have a list of companies having horizontally formatted revenue, profit, and margin information. This data has to be transposed in a vertical format.

1.Select the data, then copy it by pressing Control + C on your keyboard. Next, right-click your mouse on the cell you wish to paste this data into. You are given a menu of choices. choosing Paste Special.

2.The next screen appears once you choose Paste Special. Decide on the Values and Transpose option.

3.Then press OK.

You receive the table below.

The information is pasted as a vertical table.

You can now prepare the data and carry out the necessary analysis.

Did you study how to use IF statements in Excel while working with text? To discover more about the functionality of Word documents, Excel spreadsheets, and PowerPoint slides, follow WPS Academy. To edit word, excel, and PowerPoint documents for free, you may also download WPS Office. Get it now! and have a pleasant and straightforward working experience.