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!

In order to prevent error when string is not found, we can either use ISERROR or ISNUMBER to check the error.

=IF(ISERROR(SEARCH("D","FINANCE")),"string cannot be found","string can be found")
=IF(ISNUMBER(SEARCH("D","FINANCE")),"string can be found","string cannot be found")

Logically I would prefer ISNUMBER.

Outbound References

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

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

 

Leave a Reply

Your email address will not be published.