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

Hi, I am the uncle Mr Zhao who prefers function formulas and likes to use Excel charts to manage warehouses.

Today we learn a super powerful text splitting function, the TEXTSPLIT function. This function is currently only available in Officel version 365, and its syntax is:

= TEXTSPLIT (text to be split, column delimiter, [row delimiter], [whether to ignore empty cells], [case sensitivity], [value filled in case of error])

Next, let's learn how powerful it is through five common and practical cases of text splitting.

1. The number of people is counted as shown in the figure below. The list listed in B is a string of names connected by a ton sign. You need to count the number of people on each line.

Enter the following formula in C3 cell:

= COUNTA (TEXTSPLIT (B3, ","))

First, use the TEXTSPLIT function to split the current list with the dot sign "," as the column separator, and generate a row array.

Then use the COUNTA function to count how many elements there are in this array, and the result is the number of people.

2. Calculate the maximum number of wins in Dalian as shown in figure B3 below. A series of text is the result of multiple rounds of competitions, and it is necessary to count the maximum number of consecutive victories.

Enter the following formula in C3 cell:

= MAX (LEN (TEXTSPLIT (B3, {"negative", "flat"}))

The {"negative" and "flat"} in the formula is a constant array of characters "negative" and "flat".

We can write multiple delimiters together in the form of a constant array, and then split the text with the TEXTSPLIT function, resulting in a row array as shown in the following figure:

Then use the LEN function to count the number of characters of each element of the array, and finally use the MAX function to find out the maximum number of characters, the result is the most Dalian winning times.

3. Statistics of absence list the following figure B3 cell is the company personnel list, C3 cell is the actual participants in the meeting, the need to count the list of absenteeism.

Enter the following formula in cell D3:

= TEXTJOIN (",", TEXTSPLIT (B3MagneTeXTSPLIT (C3, ",),", ", 1))

The TEXTSPLIT (C3Magi, ",") part of the formula uses the TEXTSPLIT function to split the text of the C3 cell into a column array with the dot sign "and" as the row separator:

Then use the column array as the column delimiter and the don sign as the row separator to split the string of B3 cells with the TEXTSPLIT function. The result is as follows:

Finally, the TEXTJOIN function is used to concatenate each element in the array with the don sign as the connector to generate the absence list.

4. the sum of numbers in the extracted text, such as the B3 cell, is a string of text composed of the description of the goods and the amount, which needs to be extracted and then summed.

Enter the following formula in cell D3:

= SUM (--TEXTSPLIT (B3 TEXTSPLIT), TEXTSPLIT (B3, ".", ROW (1:10)-1), 1))

ROW (1:10)-1 in the formula generates a set of sequential values from 0 to 9

Then use the TEXTSPLIT function, using the decimal point as the column separator and the number from 0 to 9 as the row separator, ignoring the blank text of the B3 cell, resulting in a list of goods names:

Then use the TEXTSPLIT function, using this column name as the row delimiter, ignore the blank split B3 cell text, resulting in a column of numbers:

Finally, the column of values is converted to a numerical type with two negative signs, and then summed by the SUM function, the sum of the amount is obtained. The result is shown in the D3 cell in figure 3:

5, split the text into multiple lines and columns as shown below, B3 cell is a string of text connected by names and numbers, you need to split the names and numbers into two columns.

Enter the following formula in cell D3:

= LET (B3 recorder row (1:10-1), HSTACK (B3 recorder row-1)

ROW (1:10)-1 in the formula generates a set of sequential values from 0 to 9

Then use the TEXTSPLIT function to generate a list of names with numbers from 0 to 9 as row delimiters, ignoring the blank space to split the text of the B3 cell:

Then use the LET function to assign the column name to the variable a, use the TEXTSPLIT function, use the column name as the row delimiter, ignore the blank split B3 cell text, and get a list of numbers:

Finally, using the HSTACK function, the resulting name and quantity are concatenated into an array of multiple rows and two columns to get the final result:

6. Write at the end

Through the above five cases, this paper shows the powerful text splitting function of TEXTSPLIT function:

First, the TEXTSPLIT function is used to split the text into rows and columns according to the specified delimiter, and the result is returned as an array, then nested with other functions and applied to the actual scene.

This article comes from the official account of Wechat: Akiba Excel (ID:excel100), author: Zhao Jiaoyang

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.