how to use excel search functionExcel SEARCH Function can locate the position of a text string from another text string.

You can search for a single character as well as for a word from a text string.

It will return the position number of the first character of the text which you are finding from the text string.

Syntax

SEARCH(find_text,within_text,[start_num])

find_text Text string which you want to find from the other text string. This can be a single text string or a word.

within_text Text string from which you want to locate the text. You can refer to a cell or you can input a text in your function.

[start_num] A number which represents the position in within_text to start the search for the find_text.

More Information About Excel SEARCH Function

  • The search function is not a case sensitive. You can use find function if you want to search for a case-sensitive text.
  • A single cell can be referred at a time.
  • You can also use wildcard characters (* ? ~) in the search function.
  • start_num should be a number between 0 to max length of the text string from which you want to search your text.
  • When you are searching for a whole word from another text string it will only return the starting position of that word.

Examples

Use Excel Search Function to search a text

In above example, we are searching for alphabet “P” & we have specified  start_num as 1 to start our search.

Our formula returns 1 as the position of the text. But, if you look at the word, we also have a “P” at 6th position.

That means search function can only return the position of the first occurrence of a text.

Combine Excel Search Function with other functions

Now, in above example, we have used the search function to get the name of the month from another string .

I have used the search function to first locate the position of the first letter & then Mmid function & len function to extract a text string.

Sample File

how to use excel search function sample file to learn

What is Next?

To learn more about Excel SEARCH Function you can check Microsoft’s Help Section.

There are also some functions like MID | LEFT | RIGHT which are highly useful with the search function.

Apart from this, I have a list of excel functions and some real life formulas examples.

And, if you have a unique idea to use the search function, I would love to hear from you.