Shulou(Shulou.com)11/24 Report--

The original title: "Don't blink!" 3 big moves to split the text to make you efficient! "

Hello, Hello, I am studying how to split the text.

I came across this question on the Internet the other day:

To sort it out briefly, this student probably needs to achieve the following effect:

In fact, this problem is to split the same kind of items.

Take the personnel purchase course table (left table) as an example, now we need to organize the left table into the right table to facilitate follow-up statistics.

How should I do this?

Let's take a look at it with me.

This article will introduce three ways to split text according to delimiters and organize them into inventory tables:

❶ routine disaggregation practice-disaggregation and collation of tables

❷ Powerquery method-split delimiter

❸ plug-in method-square grid / E spirit.

Next, let's continue to take a look at the specific practices.

When it comes to sorting, I believe that as long as you come into contact with the little friends of Excel, you basically know this magical function, because it is so easy to use!

Although the separation method is simple this time, the operation process is relatively tedious.

There will be N steps next, please be patient and take your time! Be prepared in advance!

The knowledge points involved are: classification, relative citation, selective paste, search and replacement, and so on.

Needless to say, let's take a look at its operation steps.

❶ split-split delimiter. Select column B, click the "separate" function under the [data] tab, and the text sorting wizard appears. Select the separator. Next, the other delimiters are ",", done!

The main purpose of this operation is to split the text by a delimiter.

❷ changes from multiple columns to one column. At this time, although the text has been split, it is still in the form of multiple columns.

So we need to convert multiple columns to one column.

An equal sign "=" is needed here.

❸ further collates the form. Column B has changed from multiple columns to one column, but now column B is still in the form of a formula.

Here we need to use selective paste to convert the formula to a value, and then delete the excess.

① fill in the first name:

② converts the formula to numeric value-clears the rest:

③ looks for 0 value (shortcut key: Ctrl+F), [Ctrl+A] Select all, and right-click to delete the row:

At this point, the form is sorted out.

Now you have successfully learned the routine sorting method!

The sorting method is not difficult, but it is a bit tedious

And can not be updated in real time, after adding data, you also need to re-operate the above steps.

With regard to real-time updates, friends will definitely think of Powerquery (PQ), so let's take a look at PQ!

In front of the Powerquery method, we have done it with a breakdown, but in fact, there is also a breakdown in the PQ.

The grouping in PQ has more functions and stronger strength!

For example, it can be split according to multiple arbitrary delimiters, can also be split according to the specified location, and so on, these functions can not be compared with the traditional sorting function.

We did it in N steps before, right?

If you use PQ, you only need three steps!

Let's take a look at the specific operation.

❶ imports the table into the PQ editor. Select the table area, click "from Table / area" under the "data" tab, and then make sure.

❷ split-split delimiter. Select the column of the purchased course, and click "split"-"Separator" under the "conversion" tab.

Click "Advanced options", split it into "Line", the pilot is none, and click OK.

At this time, the form will be ready.

Then there is the operation of uploading to the table.

❸ uploads to the table. Select [Home Page]-close and upload to-[create links only]-OK:

Right-click Table 2 (the linked table just now), select load to-Table-existing worksheet, and the sorted table appears.

❹ updates in real time. If the data of the original table is modified, we can just right-click on the new table and refresh it:

Although PQ can be updated in real time and easy to operate, many friends can't use it because of version problems, or they can use it themselves, but colleagues can't.

This is about to talk about plug-in method!

Plug-in method the plug-in used here is square grid / E spirit, let's take a look at the operation of square grid first.

❶, click the "Square Grid" tab-click "merge Transformation"-"arrange and combine":

❷, click "split combination"-select A1:B6 in the region, and the option delimiter is "/":

❸ Click OK, a new workbook and a worksheet with split results will appear, and the table will be converted.

There are also E-Spirit plug-ins that can be split, and E-Spirit can put the results in the same table, and the steps are in place at once:

Select the "E Ling" tab-click "selection conversion tool"-"split to multiple rows", select the [B] column for which column to split, and select [,] for the separator:

The speed is super fast, and the efficiency is really leveraged.

However, the square grid can specify multiple consecutive different delimiters for splitting, which is more flexible than the E-Spirit method!

When we encounter practical problems, we can choose the appropriate method flexibly.

Finally, to sum up, this paper introduces three ways to split the text and organize it into a list table:

❶ routine sorting practice-tedious operation, involving some simple basic operations

❷ PQ practices-can be updated in real time, once and for all

❸ plug-in approach-square grid / E-Ling, simple and efficient.

This article comes from the official account of Wechat: Akiba Excel (ID:excel100), author: Xiao Shuang, Editor: Zhu Lan

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.