How to use SEARCH function in Microsoft Excel

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:   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.   1^st Example:- In the first example, we will search “AGE” in cell A7.   Follow the steps given below:-

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

  It returns to 1 because “SEARCH” is looking from the first character, and it found AGE beginning from the 1^st character. So it gives us 1 here. 2^nd Example:- In this example, we will search for “C” and we give the starting number as zero or negative number as the starting position.   Follow the steps given below:-

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

  The Function has returned #VALUE error because neither negative nor 0 can be the starting position. 3^rd Example:- In this example, we will show you what if we have to find the text which is there multiple times in the string.   Follow the steps given below:-

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

  The function has ignored the “o” which is at 4^th position and the function returns to 9 as the position. Because it ignored the first “o” and started looking from 5^th character onwards. However, it returns the overall position of the string. 4^th Example:- In this example, we’ll search for wildcard characters’ position within cell A10.   Follow the steps given below:-

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

  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

  We can also look for question mark:-

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

  We can see that function gave us “37” as the position of (?) question mark. 5^th Example:- In this example, we’ll learn how to enter SEARCH function to search “?of?”.   Follow the steps given below:-

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

  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. 6^th Example:- In this example, we’ll learn another use of wildcard in SEARCH function.   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

  “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.  

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! https://www.youtube.com/watch?v=HW0QP1JxeuU

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 info@exceltip.com