Another set of Text functions SEARCH and SEARCHB in Microsoft Excel are useful to find the text within the given text. These functions returns the index value where it finds the text in the given text.
These functions are non case-sensitive. That means, these considers the text “APPLE” and “apple” as same.
SEARCH formula
This formula is used to search the text in the given text. The Syntax of this function is:
=SEARCH(find_text, given_text)
Where find_text is the text to be find in the given_text. If it finds the text, it will return the index value where it finds the text.
| Formula | Result |
| =SEARCH(“soft”, “Microsoft Excel”) | 6 |
Another variation of SEARCH function we have; where it takes extra argument from where to find the text in the given text.
The Syntax of this variation is:
=SEARCH(find_text, given_text, search_from)
Where search_from is the index from where to start to find the text in the given_text. This argument is optional. By default, it starts searching the text from starting index of the text. Here is the example:
| Formula | Result |
| =SEARCH(“p”, “Apple”, 2) | 2 |
| =SEARCH(“p”, “Apple”, 3) | 3 |
Both the versions of this function returns #VALUE! Error if the text is NOT found in the given text.
SEARCHB formula
SEARCHB functions behaves the same as SEARCH function unless the default language is set as DBCS (Double Byte Character Set); where each character considers as 2 bytes.
We will discuss more topics as we go.
🙂 Sahida