Question

FIND function in Excel

Answer 1

What is Excel's FIND formula for?
The FIND function syntax
Excel's FIND formula in practice
FIND and FIND: nests
FIND and ISNUMBER: truthfulness claims
FIND and MID: character extraction
FIND and IF: yes-then-yes no

---

FIND function in Excel

If you often work with Excel, you know the usefulness of many functions. Many of the formulas are used for calculation and analysis processes. However, if you want to make it easier for yourself to work with large data collections, you should know about the FIND function. What is its syntax? How do you use the function? What is the difference between FIND and FINDB?

Index

1. What is Excel's FIND formula for?
2. The FIND function syntax
3. Excel's FIND formula in practice
   1. FIND and FIND: nests
   2. FIND and ISNUMBER: truthfulness claims
   3. FIND and MID: character extraction
   4. FIND and IF: yes-then-yes no

What is Excel's FIND formula for?

It's easy to get lost in long spreadsheets with hundreds of lines. Like any other Office product, Excel also has a built-in search function . However, this feature is not always what the user wants. Since this function searches the entire document, you have no possibility to restrict the search area. And what is more important: the obtained values ​​cannot be used in other functions, because the search function only acts on the interface..

But if you want to do a search in some specific cells and integrate the search in the spreadsheet, you can use the FIND function. When you enter the word or phrase, the function determines where in the sequence of characters (in the text of a cell) your query first appears .

With the FIND function, Excel offers a basic component that, combined with other functions, unfolds its full potential. Thus, you can request that the program tell you if a specific term appears or extract specific parts of a sequence of characters..

advice

Excel offers another search function with VLOOKUP.

The FIND function syntax

The FIND syntax is not overly complex. In the standard version you only have to indicate two arguments: what are you looking for? and where do you look for it?

  =ENCONTRAR(texto_buscado,dentro_del_texto)  

Also, you can tailor your search so that it only starts from a certain character..

  =ENCONTRAR(texto_buscado,dentro_del_texto,[núm_inicial])  

Different indications are hidden behind the parameters:

• Search_text - This is the sequence of characters you want to search for. You must put the text in quotation marks. You can also refer to a cell in which the text appears. It is always case-sensitive.
• Within_Text : This parameter determines the text you want to search. In most cases, you will indicate a cell in which the text appears here. But, in principle, it is also possible to insert the text directly at this point. In this case, you must use quotes again.
• Initial_Num : using a numeric value, you determine from which character the search for the searched text should begin. The indication is optional. If you don't enter a value, the search will start in the first position.

Note

The FIND function is case-sensitive and supports wildcard characters. If you want to skip these requirements, use the FIND function.

Excel will communicate the result to you as a number. The value indicates where the search text begins , starting with its first occurrence from the left. If the term appears in the cell again, the FIND function alone will not detect it. You can only detect more positions through nesting. The result value takes into account all characters, including spaces. The number indicates the position of the first character of the term found. That is, the first letter or first number of the search query is taken into account in the result.

Apart from FIND, the spreadsheet program also has FINDB . Both functions achieve the same result and their syntax is also identical. The only difference is in the character set you can use. FIND works with so-called single-byte character sets (SBCS). This game includes the Latin alphabet with some special character such as letters with umlauts. However, if you need to use Asian letters from the China, Japan, and Korea (CJK) regions, you should use FINDB, as this function uses double-byte character sets (DBCS). Each character spans two bytes, so you have to adapt the way you count.

advice

To work faster in Excel, you should familiarize yourself with the most important Excel keyboard shortcuts.

Excel's FIND formula in practice

There are many users who initially do not see the utility of this function. It may appear that locating the position of a term within a text is of limited utility. This function performs its actual usefulness in combination with other functions.

FIND and FIND: nests

Let's first think about how it is possible to determine not the first, but the second, third, or nth occurrence of the search term .

  =ENCONTRAR (texto_buscado;dentro_del_texto;ENCONTRAR(texto_buscado;dentro_del_texto)+1)  

At this point it is also clear what the optional third parameter can be used for. In this formula we re-enter the formula that indicates the position of the first occurrence in position number_initial . This value plus one indicates the point at which you want the superordinate function to begin its search. If you also want to determine a third position, you must perform another nesting and so on as many times as you want.

FIND and ISNUMBER: truthfulness claims

With the FIND function, Excel offers the possibility to convert the placeholder into a truthfulness statement : is the search term included in the text or not?

  =ESNUMERO(ENCONTRAR("peluche";B2))  

The ISNUMBER function returns the value TRUE if the result of FIND is a number; otherwise, the result is FALSE. Since FIND indicates the position of the term with an integer , the ISNUMBER function can respond. If the search term does not appear in the text, FIND issues an error message, which is obviously not a number, so ISNUMBER reacts with FALSE.

You might also be interested in finding out where search terms appear. This works if you have stored your data in multiple cells, eg. eg, on a sales list. The formula can be entered in so-called conditional formatting like any other formula. Thus, you can mark, for example, all transactions that are related to stuffed animals.

If you combine the FIND function with the ISNUMBER function, you can display very specific cells.

FIND and MID: character extraction

Item descriptions can be very long and confusing, so you're still interested in extracting specific parts of the character sequence. Excel has three functions for this purpose: LEFT, RIGHT, and MID . These formulas are already very interesting on their own, but in combination with FIND they develop their full potential. We start from the basis that your denominations always follow a certain pattern, which is made up of letters, numbers and hyphens: ABCDE-A-12345-T.

Let's say you want to extract the numeric part in the middle, but since the character sequence does not have a certain length , the normal functions to extract will not be very useful. These functions are based on a certain number of characters, a piece of information that, in this case, you cannot provide. However, thanks to the dashes, the FIND function can be useful, as this function will give you the position indication you need.

As several hyphens appear in the sequence of characters, you must perform a nesting of the FIND function . In this example, we assume that the numeric part always spans five characters.

  =MID (A2;ENCONTRAR("-"; A2;ENCONTRAR("-"; A2;ENCONTRAR("-";A2)+1))+1;5)  

In Excel, the FIND function can help you extract certain parts of a cell.

If the length is not determined either, you can use another nesting of the FIND function. Since the desired character sequence ends with a hyphen, you can search for the hyphen and thus determine the length.

 

  =MID(A2;ENCONTRAR("-";A2;ENCONTRAR("-";A2;ENCONTRAR("-";A2)+1))+1; ENCONTRAR("-";A2;ENCONTRAR("-";A2;ENCONTRAR("-";A2)+1)+1)-ENCONTRAR("-";A2;ENCONTRAR("-";A2;ENCONTRAR("-";A2)-1))-3)  

It is true that this formula is a bit chaotic and confusing, but it achieves the objective. No matter how many characters you enter between the two hyphens, Excel will use the FIND function to always extract the correct characters.

Complicated but effective nesting: so you can extract just the part you want.

FIND and IF: yes-then-yes no

The FIND function can also be very easily combined with the IF function. Do you want to perform a specific action when a specific sequence of characters appears in the cell? The combination of YES and FIND allows you just this: if the sequence of characters appears, this is done, if not, the other. The only problem is that FIND will indicate an error if the character sequence does not appear. Therefore, you must also use the ISERROR function .

  =SI(ESERROR(ENCONTRAR("oso";A2));"No";"Sí")  

If the FIND function does not detect the search term (in this example,? Bear?), It will report an error message. Therefore, the ISERROR condition is met and IF provides the first option:? No ?, that is, the term does not appear. On the other hand, if the FIND function is able to determine the search text, it will indicate a number, so the ISERROR condition is not met. Therefore, the other option is obtained:? Yes ?, that is, the term does appear.

By combining FIND, IF, and ISERROR you can search for parts of words.

In summary

The FIND function is especially useful in combination with other functions. The combination possibilities and the applications are very wide. Many of the problems that arise when building formulas in Excel can be solved with this simple but useful function.

---