Regardless of whether you are a master of spreadsheets and Excel or are just starting out in this world, the command to find a document is part of the foundations of keyboard shortcuts and Excel shortcuts..
Whether you choose the classic Office package or one of the free alternatives, you can search a text using [Ctrl] + [F], in all software, from Google Sheets to a common browser.
As an alternative to this shortcut, Excel offers the SEARCH function. In addition to its main use, that is to find a term in the Excel table, it also has other functions. In this article we explain how to use it..
If you work with large spreadsheets, you probably know that specific words, strings, or formulas can often be difficult to find. The same goes for typos and coding errors. Excel has the more classic [Ctrl] + [F] search function , but if you only want to search a certain area of cells or a certain piece of text, this keyboard shortcut is not entirely useful.
In this case, the Find or Find and Replace function has many limitations. If for example you want to substitute? And? for? o ?,? may? will become? maoo ?. So looking for these errors in large documents or long lines of code can be very annoying..
The SEARCH function in Excel is very useful if you are doing text analysis for statistical purposes and you want to know where in the text a letter or word, a sequence of characters or a number appears. Especially when creating and evaluating content, FIND in Excel can be of great help.
FIND Excel has a very simple structure. It only requires two pieces of information: What are you looking for? And where do you search (text or area)? The syntax is as follows:
=HALLAR(Texto_buscado;Dentro_del_texto)
The FIND and FINDB functions have a syntax similar to that of FIND or FINDB. The difference is that Excel FIND does not take into account the difference between upper and lower case and the FIND function does.
In addition, you can delimit from which character or at what point the search is carried out:
=HALLAR(Texto_buscado;Dentro_del_texto;[Primer_carácter])
These are the possible data:
The result indicates where the Search_Text begins . If there are multiple instances, the Excel SEARCH function must be nested.
The following examples show how Excel's SEARCH function behaves in practice:
=HALLAR("e";A2;6)
In this case, we look for the position of the first? E? in the character sequence of cell A2, starting from the 6th position. The result is? 8 ?, since in position 8 appears the first? E? if we start from position 6. In this way you can carry out, for example, text analysis.
=HALLAR(A1;A2)
The position of? Text? from cell A1 into? sample text? is position 1. In this context, you don't have to distinguish between upper and lower case.
The same happens if you search for? Text? as written text. Thus, you can detect, for example, misspelled names in databases.
In contrast to the FIND function, Excel's SEARCH function allows you to use the ??? wildcard. This is very useful for detecting different ways of writing something. In this way, you can avoid the #VALUE! Error message . if the value does not match the spelling of the parsed cell.
Using simple nesting with the REPLACE function, you can substitute terms with Excel's SEARCH function. The following example replaces? Example? by? Modelo ?.
=REEMPLAZAR(A2;HALLAR(A3;A2);8;"Modelo")
If you want to take full advantage of the potential of this formula, you can use it for more complex uses, for example shortening or lengthening serial numbers or encrypted content.
=EXTRAE(A2;HALLAR("-";A2;HALLAR("-";A2;HALLAR("-";A2)+1))+1;HALLAR("-";A2;HALLAR("-";A2;HALLAR("-";A2)+1)+1)-HALLAR("-";A2;HALLAR("-";A2;HALLAR("-";A2)-1))-3)
Excel's SEARCH function may seem complicated at first. But it can be very useful, especially if you want to clean or unify databases . The FIND function is very easy to combine with the FIND function, you just have to keep in mind the following:
Apart from FIND, Excel also has the formula HALLARB . Both functions have the same structure, the difference lies in the readable characters. Excel HALLAR includes, among others, the Latin alphabet, with special characters such as cedilla or accents in Spanish. For Asian symbols such as those used by Chinese, Japanese and Korean, use the HALLARB function . This is due to the way the characters are counted.