SEARCH Excel Function


Home >> How to Use Excel Tutorial >> Excel Formulas List >> SEARCH Excel Function



In this tutorial, you will learn how to use the SEARCH excel function. This formula can be used to produce a number that represents the position of characters on data in the spreadsheet.


Why do We Need to Learn SEARCH Excel Function?


In doing processing in a spreadsheet, sometimes we need to find the position of characters in a data of ours. This is sometimes needed to find the information that we need from the data or maybe as an input material for another formula.

As an example, imagine that we have all the codes from each of our company products in a spreadsheet from where there is detailed information from those products that we can see by looking at parts of the products’ codes. If the codes are many, complicated, and long in terms of their length, then it is probably pretty hard and takes a lot of time to find the information part as needed from each of the product codes if we try to look and do it manually.

Or probably the need to find the position of the characters can be found when we want to use formulas like MID to change a part of a text data. If we want to determine the starting point of the changing process or the number of characters that we want to change based on the position of a part in the text, then it will make it much easier for us if the position finding process can be done automatically. Especially, like the previous case, if the text data is much in its amount or long for each of their lengths.

To help to do the position finding process in an automatic way, then we can use the SEARCH excel function. This function will help you to find the position of characters that you need in data. It will be very useful to be understood and utilized if you often do the process that is similar to the needs of the two cases above on a spreadsheet.


What is SEARCH Formula?


SEARCH formula in excel is a formula with the usage objective to get a number that represents the position of one or more characters in a data. SEARCH function in excel will return the position digit of the first character from the group of characters that you want to find in your data.

Briefly, all of the inputs in this function can be explained as follows:


=SEARCH(find_text, within_text, [start_num])


Notes:
  • find_text = one or more characters to be searched
  • within_text = the data where the search process is done
  • start_num = optional. The position in the data where the process will start



How to Use the SEARCH Excel Function?


The following will explain how to write SEARCH excel function. SEARCH excel function needs required inputs of the characters that you want to find the position of and the data that you want to do the lookup process on and also an optional input which is the position where the lookup process will start.


Using SEARCH Excel Function

  1. Type equal sign ( = ) in the cell where you want to put the result in

    SEARCH Excel Function - Screenshot of Step 1

  2. Type SEARCH (can be with large and small letters) and open bracket sign after =

    SEARCH Excel Function - Screenshot of Step 2

  3. Type character or group of character which want to be found / cell coordinate where it is in after open bracket sign followed by a comma sign ( , ). If the input in this part is not just number and you want to type it directly, then it must be typed between quotation mark ( “ )

    SEARCH Excel Function - Screenshot of Step 3

  4. Type data/cell coordinate where the content which you want to search using with this formula is after open bracket sign

    SEARCH Excel Function - Screenshot of Step 4

  5. Optional: Type a comma sign ( , ) and then the number which represents the starting position of character search in the data or cell content / cell coordinate where this number is. If there is no input in this part, then the process will be done from the front position

    SEARCH Excel Function - Screenshot of Step 5

  6. Type close bracket sign

    SEARCH Excel Function - Screenshot of Step 6

  7. Press Enter
  8. The process is done!

    SEARCH Excel Function - Screenshot of Step 8



Exercise


After you have understood how to use the formula by learning from the tutorial above, now is the time to deepen your understanding by doing the following exercise.

Download the exercise file and answer all of the questions. Download the answer key file if you have done the exercise or if you are confused about how to answer the questions!

Link of the exercise file:
Download here

Questions

  1. What is the number that represents the position of letter e?
  2. What is the number that represents the position of “John”?
  3. What is the number that represents the position of “-HIXY”? Use a wildcard to replace “-“ in the group of characters that you want to find from the data! (Look at the additional notes if you do not understand how to use wildcard)

Link of the answer key file:
Download here


Additional Note

  • The SEARCH excel function is similar in its usefulness with FIND. The differences are SEARCH is not case-sensitive and you can use a wildcard like ? and * that you cannot use in FIND
  • If you want to find characters that contain ? or * in your data, then use a tilde (~) before the ? or * so the formula does not think it as a wildcard
  • If you want to do the lookup process in a date, then this formula will do the process on the serial number of the date




Want to Learn More About Excel?


Get updated excel info from Compute Expert by registering your email. It's free!


Want to Learn More About Excel?


Get updated excel info from Compute Expert by registering your email. It's free!



Follow our tutorial content also on


CONTACT US PRIVACY POLICY TERMS AND CONDITION AFFILIATE DISCLOSURE @Compute Expert