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

As the saying goes, where there is division, there is cooperation.

So in the Excel function, the combination is Textjoin and the division is Textsplit.

Textsplit=Text (text) + Split (split), as the name implies, is a function used to split text, either by column or by row.

It has five parameters:

= TEXTSPLIT (text,col_delimiter, [row_delimiter], [ignore_empty], [pad_with] first parameter text: text to be split

Second parameter col_delimiter: column delimiter

The third parameter [row_delimiter]: [line delimiter]

Fourth parameter [ignore_empty]: [whether to ignore empty cells]

The fifth parameter [pad_with]): [value filled in case of error].

There are more and more magical ways to match Textsplit with other functions.

Now follow me, let's experience it.

I will use three cases to complete the data collation from the left table to the right table in the following figure.

Statistics on the number of subjects case 1: count the number of course items purchased by each person.

As shown in the following figure, enter the formula in cell C2 and fill it down:

= COUNTA (TEXTSPLIT (B2 ","))

Train of thought analysis:

Let's first use the Textsplit function to split the string by the delimiter ",".

Finally, the Counta function is used to count the number of non-empty cells, that is, the number of subjects.

Is it very simple? let's take a look at the second case.

The specified number of repeated names case 2: organize the names into one column according to the specified number of items.

As shown in the following figure, in cell D2, enter the following formula:

= TEXTSPLIT (CONCAT (REPT (A2VR A6 & ",", C2:C6)) ",", TRUE)

Train of thought analysis:

We first concatenate the names and delimiters that need to be repeated, and then use the Rept function to repeat the number of corresponding names.

The Rept function repeats the text a specified number of times

= Rept (text, times)

As shown below:

Then merge using the Concat function.

The function of Concat is to connect a list or text string area, that is, = Concat (text area)

Finally, the merged text is split into lines using Textsplit, which is the result we want.

Let's move on to the next case.

Split delimiter to line case 3: split the purchase account column into one column according to the delimiter.

As shown in the following figure, in cell E2, enter the following formula:

= TEXTSPLIT (TEXTJOIN (",", TRUE,B2:B6) ",")

Train of thought analysis:

Let's first merge the data areas of the purchase account column with the Textjoin function according to ",".

The Textjoin function, which can merge cell ranges according to the specified delimiter

= Textjoin (delimiter, whether to ignore null values, array / cell range)

Enter True if you want to ignore null values, and False if you do not ignore null values.

As shown below:

Finally, the merged text is split into lines using the Textsplit function, and it is done directly!

If you are careful, it must not be difficult to find you.

Split the specified characters and organize them into an one-dimensional table, which is actually the merge of the above case.

The advantage of this arrangement into an one-dimensional table is that it is convenient for us to do PivotTable analysis and statistics later.

Now, do you realize the power of the Textsplit function?

To sum up, this article introduces the high-level use of the Textsplit function, that is, with other functions, you can play a more powerful data collation ability. Three cases are mentioned:

Number of items in ❶ Statistics (Textsplit,Counta)

❷ repeat a specified number of times (Rept,Concat,Textsplit)

❸ splits delimiter to line (Textjoin,Textsplit)

The Textsplit function is so easy to use, so do you want to learn it?

It is not recommended to learn, because.

OFFICE 365 is required to support it!

Earlier versions of Excel and WPS are not currently available ~

This article comes from the official account of Wechat: Akiba Excel (ID:excel100), author: Xiao Shuang, Editor: Xiaoyin, 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.