How to Use the SEARCH Function in Excel: Usabilities, Examples, and Writing Steps - Compute Expert

How to Use the SEARCH Function in Excel: Usabilities, Examples, and Writing Steps


Home >> Excel Tutorials from Compute Expert >> Excel Formulas List >> How to Use the SEARCH Function in Excel: Usabilities, Examples, and Writing Steps





In this tutorial, you will learn how to use the SEARCH function in excel completely.

When working with data in excel (especially texts), we may sometimes need to know the position of a part in our data. One of the things you can use to help you get this information fast is the excel SEARCH function.

Want to know more about SEARCH and how to use it properly to help you in excel? Read this tutorial until its last part!

Disclaimer: This post may contain affiliate links from which we earn commission from qualifying purchases/actions at no additional cost for you. Learn more







What is the SEARCH Function in Excel?

SEARCH is an excel function that helps you to find the position of a data part in your data (usually text). SEARCH isn’t case-sensitive and it recognizes wildcard characters in its input.



SEARCH Usability

You can use SEARCH to get the position of a data part in your data fast and easily.



SEARCH Result

The SEARCH result is a number that represents the position of a data part in your data.



Excel Version from Which We Can Start Using SEARCH

We can start using SEARCH in excel since excel 2003.



The Way to Write It and Its Inputs

Here is the general writing form of SEARCH in excel.

= SEARCH ( data_part_to_find , data , [ starting_position_to_find_the_data_part ] )


And here is a bit explanation for the inputs you need to give while using SEARCH in excel.
  • data_part_to_find = the part of data you want to find the position of
  • data = the data from which you want to find the position of its part
  • [starting_position_to_find_the_data_part] = optional. The starting position in your data from where you want to find the data part. If you omit this, then SEARCH will try to find the data part from position 1 (the most left position in the data)



Example of Its Usage and Result

Here is an implementation example of SEARCH in excel.

How to Use the SEARCH Function in Excel: Usabilities, Examples, and Writing Steps - Screenshot of the SEARCH Implementation Example

In the example, we want to find a data part position in the data that we have. We use SEARCH to help us find it.

As we discussed previously, we just need to input the data part and the data itself when we use SEARCH. Input the starting position where you want to start finding the data part position too if it helps you.

By writing our SEARCH like that, we get all the data part positions we need in our example! As you can see in the second row there, however, SEARCH isn’t case-sensitive in its data part finding process. Consider this nature when you want to find your data part position by using this SEARCH formula.

Learn Excel Dashboard Course



Writing Steps

Confused when you need to write SEARCH in your excel cell? Take a look at the SEARCH writing steps below to help you do it!

  1. Type an equal sign ( = ) in the cell where you want to put the position of your data part

    How to Use the SEARCH Function in Excel: Usabilities, Examples, and Writing Steps - Screenshot of Step 1

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

    How to Use the SEARCH Function in Excel: Usabilities, Examples, and Writing Steps - Screenshot of Step 2

  3. Input the data part you want to find the position of in your data. Then, type a comma sign ( , )

    How to Use the SEARCH Function in Excel: Usabilities, Examples, and Writing Steps - Screenshot of Step 3

  4. Input the data from where you want to find the position of the data part you inputted earlier

    How to Use the SEARCH Function in Excel: Usabilities, Examples, and Writing Steps - Screenshot of Step 4

  5. Optional: Type a comma sign. Next, input the number which represents the starting position from where you want to find your data part inside your data. If you omit this input, SEARCH will start the finding process at position 1 (the most left position in the data)

    How to Use the SEARCH Function in Excel: Usabilities, Examples, and Writing Steps - Screenshot of Step 5

  6. Type a close bracket sign

    How to Use the SEARCH Function in Excel: Usabilities, Examples, and Writing Steps - Screenshot of Step 6

  7. Press Enter
  8. Done!

    How to Use the SEARCH Function in Excel: Usabilities, Examples, and Writing Steps - Screenshot of Step 8




Case-Sensitive SEARCH: FIND

Want to find the position of your data part in a case-sensitive manner? You may need to do that since case sensitivity may get you the correct position of the data part you want.

If you need to be case-sensitive, then you need to use FIND instead of SEARCH. FIND is an excel formula that helps you to find the position of a data part too in your data. However, FIND is case-sensitive, unlike SEARCH.

Here is the general writing form of FIND in excel.

= FIND ( data_part_to_find , data , [ starting_position_to_find_the_data_part ] )


Similar writing form as SEARCH, right? If you have understood how to write SEARCH previously, you should have no trouble when you write FIND.

Here is the implementation example of FIND in excel.

How to Use the SEARCH Function in Excel: Usabilities, Examples, and Writing Steps - Screenshot of the FIND Implementation Example on Case Sensitive Data

As you can see here, FIND is case-sensitive when trying to find the data part we input into it. If a data part has different letter cases, even if its content is similar, FIND will ignore it.

This is different from SEARCH which isn’t case-sensitive. Here are the SEARCH results if we try to implement it to the data set above.

How to Use the SEARCH Function in Excel: Usabilities, Examples, and Writing Steps - Screenshot of the SEARCH Implementation Example on Case-Sensitive Data

As SEARCH isn’t case-sensitive, it will take the data part position by looking at the content only.

To find a data part position in your data correctly, consider the best formula for you to use: SEARCH or FIND!



SEARCH with Wildcard Characters

Besides their case sensitivity difference, there is also another important difference we should note when we want to use SEARCH/FIND. That difference is SEARCH can process wildcard characters while FIND cannot.

If we still don’t know several characters that our data part input has, wildcard characters can help us with that. Wildcard characters are the characters that represent one/several of any characters in our excel formula.

Learn Excel Dashboard Course


There are two wildcard characters we often use in excel.
  • * = represents any character with any amount
  • ? = represent any one character

We can use them in SEARCH in our data part position input. Use the one that best suits your needs whenever you want to find your data part position!

Here is an implementation example of the wildcard characters in action in our SEARCH formula.

How to Use the SEARCH Function in Excel: Usabilities, Examples, and Writing Steps - Screenshot of the SEARCH Implementation Example with Wildcard Characters

Whenever you need something to represent characters you don’t know in your SEARCH data part input, utilize the wildcard characters



Know if a Cell Contains a Specific Text: ISNUMBER SEARCH/IF ISNUMBER SEARCH

Want to know whether your cell contains specific text or not in excel? You can also use SEARCH to get that information by combining it with ISNUMBER and IF.

Combine SEARCH with ISNUMBER only if you want to get the identification result in TRUE or FALSE (TRUE for if the cell contains the text and FALSE for if the cell doesn’t). If you want a special identification mark instead, however, then you can combine IF too with them.

Here is the general writing form for the ISNUMBER SEARCH and the IF ISNUMBER SEARCH formulas in excel.

ISNUMBER SEARCH

= ISNUMBER ( SEARCH ( data_part_to_find , data ) )


IF ISNUMBER SEARCH

= IF ( ISNUMBER ( SEARCH ( data_part_to_find , data ) ) , “Yes” , “No” )


Write your SEARCH formula inside ISNUMBER. You don’t need to input a starting position to SEARCH here since we just use it to ensure a text’s existence. Input the ISNUMBER and SEARCH in your IF logic condition part if you combine them with IF too.

In this IF ISNUMBER SEARCH writing form, we have “Yes” and “No” as our IF true and false results. You can obviously change them into other things depending on your preference.

Here is an implementation example of ISNUMBER SEARCH and IF ISNUMBER SEARCH in excel.

How to Use the SEARCH Function in Excel: Usabilities, Examples, and Writing Steps - Screenshot of the ISNUMBER SEARCH and IF ISNUMBER SEARCH Implementation Example

As you can see there, our formulas combinations can identify whether a specific text exists in a cell. If we combine ISNUMBER and SEARCH only, we will get a TRUE or FALSE result. If we combine IF, ISNUMBER, and SEARCH, we can get an identification mark according to our preference.



Exercise

After you have understood how to use SEARCH in excel completely, let’s 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 and want to check your answers. Or probably if you are confused when you want to answer the questions!

Link to the exercise file:
Download here

Questions

Use SEARCH to answer all the questions below!
  1. What is the number that represents the position of the 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 character to replace the “-“ symbol in your SEARCH input!

Link to the answer key file:
Download here



Additional Note

If you need to input a * or ? symbol literally in SEARCH, use a tilde symbol (~) before you input the symbol.



Related tutorials you should learn too:



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


Excel Calculation

Sum in Excel

Subtraction in Excel

Multiplication in Excel

Division in Excel

Average in Excel



Excel Formula

VLOOKUP Function in Excel

IF Function in Excel

SUM Function in Excel

COUNTIFS Function in Excel

SUMIFS Function in Excel



Excel Tips and Trick

How to Print in Excel

Convert Number to Text in Excel

Excel Worksheet Definition

Excel Range Definition

Excel Shortcuts



Excel Products & Services Recommendation

Best Laptops for Excel

Best Tablets for Excel

Best Keyboards for Excel

Best Mouse for Excel

Best Monitors for Excel



Excel Consultation

About Us

Contact Us

Privacy Policy

Affiliate Disclosure

Terms & Condition



© 2022 Compute Expert