Excel Search Function

This Excel tutorial explains the Excel Search Function for Excel worksheet, and also explains the differences between INSTR and FIND function.

Excel Search Function

Excel Search function allows Wildcard to search a substring (part of the string) within a string and return the position of the first occurrence. Excel Search Function is not case sensitive, you should consider using Excel FIND Function for case sensitive comparison, but FIND Function cannot use Wildcard.

For example, in the string “FinanceDepartment”, the substring “Department” can be found at 8th position within “FinanceDepartment”, the function will return number “8”.

If we try to search substring “Division” within “FinanceDepartment”, since it cannot be found, error #VALUE! will return, but you can get rid of the error using IFERROR Function, I will show an example below.

For Excel VBA, please use Instr Function.

Syntax of Excel Search Function

SEARCH( substring, string, [start_position] )

substring is part of the string you want to search, you can use Wildcard characters.

Wildcard Meaning Example
* Represents one or more characters (any character) J*     any text that starts with J
*J     starts with any text but ends with J
*J*   any text that has J in the middle
? Represents one character (any character) J?     2 characters that start with J
?J     2 characters that end with J
?J?   3 characters with J in the middle
~ Treat * or ? as the actual character but not wildcard. Used ~ followed by * or ? J~**   any text that starts with J*
~**J   any text that starts with * and ends with J
~?*~* any text that starts with ? and ends with *

string is the full string

[start_position] is optional. It is the starting position for the search, default i s 1. For example, if you want to search “De” from “Development Department”, beginning from the 13th position, the function will return 13, not 1.

Excel VBA does not have SEARCH or FIND function (only FIND Method for Range), but VBA Function INSTR is very similar to SEARCH, but they are different in the way that INSTR allows case sensitive or case insensitive comparison and return 0 if substring is not found.

Example of Excel Search Function

Function Result Explanation
SEARCH(“2″,”123123”,3) 5 The first occurrence of “2” in “123123” starting from position 3 is at position 5
SEARCH(“f”,“FINANCE”) 1 Since Search Function is not case sensitive, “f” can be found in position 1
SEARCH(“D”,“FINANCE”) #VALUE! “D” not found in substring
SEARCH(“AN*”,“FINANCE”) 4 Search a substring position that starts with AN
IFERROR(SEARCH(“D”,”FINANCE”),”error”) error Capture error to prevent error #VALUE!

Outbound References

http://www.techonthenet.com/excel/formulas/search.php

https://www.youtube.com/watch?v=fk3MDE71cgw

 

Wyman W
Wyman is a Human Resources professional based in Hong Kong, specialized in business analysis, project management, data transformation with Access and Excel.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

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