How to split text in multiple rows in Excel

Most of the data in excel consists of text having multiple products that we want to split in multiple rows in order to present a well organized and precise data. Splitting of text in rows helps the users in interpreting data of different kinds. On the other hand, data consisting of text in a single row makes it difficult for the users to comprehend the main concept mentioned in a data and also creates issues in sorting and storing such data.

Considering the above mentioned problem excel provides its users certain features that would result in producing an organized data consisting of the text presented in multiple rows. The methods and functions to access these features are difficult for the beginners to understand. In this article we elaborate these methods with simple steps that would help the excel users in availing the feature of splitting text in multiple rows. These techniques are compatible for 2016/2019/mac/online versions.

How To Split Text In Multiple Rows In Excel Using Substitute And Filterxlm Functions?

These functions consist of certain formulas that would help excel users in splitting text in multiple rows in excel. The steps involved in these functions for the activation of the feature of splitting text in rows are as follows. This whole process works with 2016/2019/mac/online versions.

1. First, we will select the data we want to split in multiple rows and organize it in specific cells.

2. Then in the formula for we will enter the formula FILTERXML(t> &SUBSTITUTE(A1,,, ) &, //s).

3. After that we will press the enter key which will split the selected data in multiple rows.

This method is applicable for 2016/2019/mac/online versions.

How To Split Text In Multiple Rows In Excel Using Transpose Function?

Transpose function also consists of formula that causes the separation of text in multiple rows. However, for the activation of Transpose function we should first access the feature of text to columns. This whole process will help the excel users in splitting of text in multiple rows which works with 2016/2019/mac/online versions.

1. First we will select the range of cells consisting of data that we want to split in multiple rows.

2. Next, we will go to data tab and select the option of text to columns and then we will check the delimiter used in data and then we will press enter key.

3. It will result in splitting of text in different cells.

4. Then we will enter formula TRANSPOSE

5. This will result in splitting of data in multiple rows.

This technique is applicable for 2016/2019/mac/online versions.

How To Split Text In Multiple Rows In Excel By Applying Power Query?

Another method of splitting text in multiple rows in by using power query editor.

The steps for the activation of this feature for separation of data in multiple rows are as follows.

This technique is compatible for 2016/2019/mac/online versions.

1. First, we will select the cell range we want to split in multiple rows then we will go to data tab and select the option of table/ range.

2. Then we will check our cell range in the dialog box and press ok.

3. This activates the power query editor. Then we will select range in power query and we will go to data tab and click on the option of split column.

4. This will cause the separation of data in multiple rows.

This procedure works with 2016/2019/mac/online versions.

Did you learn about how to use multiple if functions in excel using different methods? You can follow WPS Academy to learn more features of Word Document, Excel Spreadsheets, and PowerPoint Slides.

You can also download WPS Office to edit the word documents, excel, and PowerPoint for free of cost. Download now! And get an easy and enjoyable working experience.