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

The original title: "more powerful than Countif!" Count the number of people in the cell. This function is NO.1.

Hello, Hi~, I am Tian Tian, who loves to learn from Excel.

It's time for [0 basic learning Excel function] to start the class on time every week.

Today, let's continue to learn about a very useful text function-- the Substitute function.

In the last issue, we learned the basic usage of the Substitute function. Students who forget can go to make up lessons 👉 first.

This super easy to use Excel text replacement function to help you easily work until the end of the day!

In fact, in addition to replacing text, Substitute can sometimes be used as a "statistical function"!

Today we are going to learn this brainstorming method! ❛ thanks to ✧

The case is a list of team members, and we now need to count the number of members of each team.

We can see that although the names of the members are different, the names are separated by Chinese commas.

So if you want to count the number of members of each group, you actually count the number of commas in each cell plus 1!

With this idea, combined with the len function we have learned before, this problem can be easily solved.

❶ We first use the len function to calculate the number of characters in each cell. Enter the = len function in the F2 cell with parentheses "(".

❷ uses the mouse to select the [E2 cell] on the left, put the anti-bracket ")", press enter, fill the formula down, and we have calculated the number of characters in each cell.

You can see that the comma in the cell is counted as one character.

Next, let's calculate the number of commas. It is equivalent to using the number of characters here, minus the number of characters that do not contain a comma in the cell, and deduce the number of commas.

❸ enters the = Substitute function in the [G2] cell, and the first argument references the [E2] cell with a comma ",".

The second parameter is first marked in ["] English double quotes, and enter a Chinese comma in the middle of it as the replacement object.

The third parameter directly enters the English double quotation marks ["], which represents a null value, with anti-parentheses, press enter and fill the formula down.

❹ enter the len function after the equal sign, package the whole content as a parameter of the len function, put in anti-parentheses, press the enter key, and calculate the number of characters without a comma.

Next, we will subtract the two numbers and calculate the number of commas.

❺ merges the two formulas directly, double-click the [G2] cell to enter formula editing mode, select the partial formula to the right of the equal sign, press [Ctrl+C] to copy, and press enter.

❻ double-click the [F2] cell, type the minus sign "-" after the formula, press [Ctrl+V] to paste the formula you just copied, and press enter. Now what we get is the number of commas in each cell.

At the end of ❼, we add the number "1" to the number of commas to calculate the final number. Double-click the [F2] cell, enter "+ 1" at the end of the formula, press enter, and refresh again. In this way, we have finished calculating the number of people in each group.

The complete formula is as follows:

= LEN (E2)-LEN (SUBSTITUTE (E2, ",") + 1 this article comes from the official account of Wechat: Akiba Excel (ID:excel100), author: Tian Tian

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.