How to use SEARCH function in Microsoft Excel

Follow by Email
Facebook
Facebook
Google+
http://www.exceltip.com/text/excel-search-function.html
SHARE




In this article, we will learn how to use SEARCH function in Microsoft Excel.

The MS-Excel SEARCH function returns the position of the first character of sub-string or search_text in a string. The function does not discriminate between uppercase and lowercase letters while searching. Unlike FIND, SEARCH allows wildcard characters, like question mark (?) and asterisk (*). The question mark (?) matches any single character and the asterisk (*) matches any sequence of character. However, in case we want to find an actual question mark (?) or asterisk (*), we type a tide (~) before the character. If the sub-string is not found within the string, then the function will return #VALUE error. SEARCH function can be used as powerful string method function when combined with MID function.

The function arguments / syntax are:

image 1

 

We have dummy data in column A. Column B contains the text which we will search for. And column C has the starting position of the search. And, here in column D, we will enter the SEARCH function.

image 2

 

1st Example:-

In the first example, we will search “AGE” in cell A7.

image 3

 

Follow the steps given below:-

  • Enter the function in cell C7
  • =SEARCH(“AGE”,A7,1)
  • Press Enter

image 4

 

It returns to 1 because “SEARCH” is looking from the first character, and it found AGE beginning from the 1st character. So it gives us 1 here.

 
2nd Example:-

In this example, we will search for “C” and we give the starting number as zero or negative number as the starting position.

image 5

 

Follow the steps given below:-

  • Enter the function in cell C8
  • =SEARCH(“c”,A8,-1), Press Enter

image 6

 

The Function has returned #VALUE error because neither negative nor 0 can be the starting position.

 
 
3rd Example:-

In this example, we will show you what if we have to find the text which is there multiple times in the string.

image 7

 

Follow the steps given below:-

  • Enter the function in cell C9
  • =SEARCH(“o”,A9,5), Press Enter

image 8

 

The function has ignored the “o” which is at 4th position and the function returns to 9 as the position. Because it ignored the first “o” and started looking from 5th character onwards. However, it returns the overall position of the string.
 
4th Example:-

In this example, we’ll search for wildcard characters’ position within cell A10.

image 9

 

Follow the steps given below:-

  • First we’ll look for (*) asterisk sign, enter the function in cell C10
  • =SEARCH(“*”,A10,1), Press Enter

image 10

 

The function has returned 1, because we cannot find any wildcard without using tilde. As we use tilde (~) as a marker to indicate that the next character is a literal, we will insert (~) tilde before (*) asterisk.

  • Enter this function =SEARCH(“~*”,A10,1)
  • Now it returns 16 as the position

image 11

 

We can also look for question mark:-

  • Enter the function in same cell C10
  • =SEARCH(“~?”,A10,1), Press Enter

image 12

 

We can see that function gave us “37” as the position of (?) question mark.
 
5th Example:-

In this example, we’ll learn how to enter SEARCH function to search “?of?”.

image 13

 

Follow the steps given below:-

  • Enter the function in cell C11
  • =SEARCH(“?of?”,A11,1)
  • Press Enter

image 14

 

We scan to see that function has returned 6 because it matches “soft” which is present in the mid of “Microsoft Excel” string and hence the value is 6.

Note:- (?) question mark wildcard denotes any single character.
 
6th Example:-

In this example, we’ll learn another use of wildcard in SEARCH function.

image 15

 

Follow the steps given below:-

  • Enter the function in cell C12
  • =SEARCH(“m*t”,A12,1)

Note: - In first argument, we tell the function to look for the string which starts with “m” and ends with “t”, and we will put asterisk (*) in between.

  • Press Enter

image 16

 

“Microsoft Excel” and it finds there is a string which starts with “M” and ends with “L”. No matter how many characters are there in between and hence it returns 1. Because (*) asterisk wild character matches any sequence of character.

So, this is how SEARCH function works in different situation.

 

image 48
 

Video: How to use SEARCH function in Microsoft Excel

Click on the video link for quick reference to the use of SEARCH function. Subscribe to our new channel and keep learning with us!
 

 

If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook.
We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write us at [email protected]

 
 

Please follow and like us:
0


Leave a Reply

Your email address will not be published. Required fields are marked *

To avoid automated spam,Please enter the value *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>