How to split one column into multiple columns in excel

Let’s share two very practical ways to split one row into multiple rows in Excel.

1. Application scenarios

Some people may have questions. Isn’t it all rows of data by default in Excel? Do the rows of data still need to be split?

Of course, if you encounter this kind of table data, which contains multiple dimensions of data in a row, then you need to split the row at this time.


As shown in the screenshot above, there are multiple employees in each branch, and the names of multiple employees are all counted in one cell.

This format is not conducive to our later analysis of the information of each branch employee, so we need to split multiple content in one line into multiple lines, and finally split into this effect:

Two, application skills

For the splitting of Excel rows, one row of content is split into multiple rows. There are two common methods:

1. Use word for auxiliary split

The overall idea is: copy the content of the line to be split into the word document, then replace the delimiter with a paragraph break through the search and replace in the word document, and then paste it into the Excel table for processing after the replacement.

2. Use Power Query own line split function

Power Query is a very powerful hidden function that comes with the Excel 2016 version. It can not only quickly split the columns in the Excel table, but also split the rows. The overall operation method is very similar to the split method of the columns. .

For the specific steps of the above two methods, please watch the following video:

3. Thinking of transferable application scenarios

Think about whether this skill can be used in your daily work. If so, hurry up and try it. Hope the above article is helpful. Get more tutorials like this please visit us on our official web page.

Leave a Comment

Your email address will not be published. Required fields are marked *