How to use Excel Search Function

When working on a large spreadsheet locating data becomes more difficult. Thus you need to use a function to speed things up. The search function is one of the functions used to locate and return values on Excel. In this article I will guide you on different ways that you can make use of the Excel Search function.

What does the Excel SEARCH function do? 

The search function returns the position of a sub-string in a text. It is categorized as a string or text function in Excel.

Excel SEARCH function syntax



find_text: refers to the text that you wish to search

within_text: refers to the text string that contains the substring that you wish to find

Start_num: You can add or leave it. It’s the number of positions that you wish to search

Important points

  • The search function is not case sensitive
  • Uses of SEARCH function
  • To find the position of a substring in a string

Example 1: How to find the first appearance of a text

How to find the first occurrence  of a text in  a string

Let us use the data in the screenshot below for our examples

=SEARCH(“rem”, “Lorem Ipsum”) returns  3

Use Excel search functio to find the position of a substring

The function is case insensitive as seen with the example below

=SEARCH(“REM”, “Lorem Ipsum”) returns 3

The Excel search function is not case sensitive

Example 2: How to find a text by specifying the starting postion

You can also search the position of a substring by specifying the position that you wish to start.

=SEARCH(“rem”,”Lorem Ipsum Lorem”, 4) returns 15

Search for a text starting at a certain position

Note that in this example, the function finds the position of the second rem since we have instructed it to start at position 4. Not that counting positions start at 1 but counting the substring starts at 4.

Example 3: How to use a wildcard in Excel SEARCH unction

A wildcard refers to a special character that one can use to represent a character in an Excel formula.

You can use a wildcard to find the position of a text in Excel.

use search function and wildcard to find text

=SEARCH(“cen”, “at the center of the shopping centre”) returns 8

Related Article: How to search inside Excel Files: 6 Simple Ways

EXample 4: How to use ISNUMBER and Excel SEARCH function

If you want to test for  specific text, then you can combine ISNUMBER and SEARCH  function. If the text is found then the formula will return:

In the data below we have applied this formula =ISNUMBER(SEARCH(C3,A2))

How to find an actual text using ISNUMBER and the SEEARCH function

Download Excel search function practice template

Leave a Reply

Discover more from Excel Wizard

Subscribe now to keep reading and get access to the full archive.

Continue reading