Hello, friends~ Today I will tell you about the FIND function.

This function, similar to MATCH, is an auxiliary function. It is not very powerful when it is singled out, but many big functions rely on it to play a huge role.

Now, let's learn together!

Function structure = FIND (what to look for, where to look, starting from the first position in the string).

Basic usage

1. Where are you looking for [Girlfriend]?

Enter the formula in cell B2: =FIND("Girlfriend",A2)

When the first parameter of the FIND function is text, English double quotation marks should be added at both ends, and the first parameter can also refer to a cell.

2. The FIND function is case sensitive

The SEARCH and SEARCHB functions are not case sensitive.

For example, in the image below, we want to find the position of the letter a in cell A3.

Error message:

Let's combine the big function function and feel the charm of the FIND function together!

Extended application

1. Find the full name by the abbreviation

I believe that many small partners have encountered this problem. As shown in the figure below, we need to find the full name of the corresponding company according to the company abbreviation in cell D2.

Enter the formula in cell E2:

=LOOKUP(1,0/(FIND(D2,$A$2:$A$7)),$A$2:$A$7)

Formula description:

Use the FIND function to find where the D2 cell text "Armor" appears in each cell text in $A$2:$A$7. Cells that contain "armor" will return a number, and cells that do not contain "armor" will return the #VALUE! error value.

Divide 0 by the return value of the FIND function to get a new search area consisting of a 0 and multiple #VALUE!.

Finally, use the LOOKUP function to search with 1, so that the content corresponding to the 0 value in the new area can be obtained.

2. Counting votes

As shown in the picture, how to calculate the number of recommended votes for each person?

Enter the formula =COUNT(FIND(D2,$A$2:$A$11)) in the cell, and then press Ctrl+Shift+Enter.

Formula description:

FIND(D2,$A$2:$A$11) means to find the position of D2 in the range $A$2:$A$11, where the COUNT function counts the number of cells that contain numbers in the range.

3. Extract name and phone number

The location of each group of phone numbers and names is not the same, so it can't be used for sorting, so how should we solve it?

Enter the formula: =MID(A2,MIN(FIND(ROW($1:$10)-1,A2&5/19)),11)

This is an array function. After entering the formula, you need to use the three keys CTRL+SHIFT+ENTER to end.

Through the operation in the previous step, we have extracted the complete phone number. Next, we only need to use the SUBSTITUTE function to replace the extracted phone number with empty space in the data area, and that's it!

Enter the formula in cell B2: =SUBSTITUTE(A2,C2,"")

In this way, is the FIND function particularly important?

Have you learned it?

This article comes from the excel tutorial, edited and shared by the financial and taxation site. Please indicate that the copyright belongs to the author. Thank the author for his hard work. If the copyright is involved, please let us know, and we will deal with it as soon as possible. We remain neutral to the views in the article, for reference and communication purposes only!

◆1, 43 Excel functions, necessary for data analysis! learn!

◆ 2. In 10 minutes, learn 10 commonly used Excel function formulas

◆ 3. [excel] 7 tips for beginners

◆ 4. [EXCEL] Check thousands of pieces of data every day, and never read a wrong line! Just because of the use of this artifact

◆ 5. 9 Excel tips necessary for finance and sales

◆ 6. [EXCEL] Several text functions that are very useful and simple for financial work

◆ 7. 8 classic EXCEL function combinations in financial work!

◆ 8. [EXCEL] This may be the best and most complete VLOOKUP function tutorial in history

◆ 9. [Excel] only uses IF to judge true and false? What a genius!

◆ 10, 4 common Excel mistakes, must pay attention

◆ 11. 20 Excel tips for accounting daily work

Add WeChat xcyaxcy to join the Finance and Taxation Site WeChat group and series QQ group (QQ exchange group: 44808534) , and communicate with accounting peers across the country online!

To purchase relevant examination materials such as professional titles, affiliations, tax accountants, etc. or to join the examination exchange group, add WeChat ID: xcyaxcy or 245826703. ( Indicate the name of the required information or what group to add )

point to share

Like

click to watch

point to share Like click to watch

point to share

Like

click to watch