Excel Functions Tutorials
- Excel Formulas Cheatsheet
- Financial Functions in Excel
- Logical Functions in Excel
- TEXT Functions in Excel
- Lookup Reference in Excel
- Address Function in Excel
- Choose Function in Excel
- Column Function in Excel
- Columns Function in Excel
- REPLACE Function in Excel
- GetPivotData in Excel
- HLOOKUP in Excel
- Hyperlink Excel Function
- INDIRECT Function in Excel
- LOOKUP Excel Function
- Match Excel Function
- VLOOKUP Excel Function
- INDEX Excel Function
- VLOOKUP vs HLOOKUP
- Maths Functions in Excel
- POWER Function in Excel
- EVEN Function in Excel
- ODD Function in Excel
- ABS Function in Excel
- SUM Function in Excel
- SUMPRODUCT Function in Excel
- SUBTOTAL Excel Function
- ROUND in Excel
- AGGREGATE Excel Function
- PRODUCT Excel Function
- RAND Excel Function
- LOG Excel Function
- EXPONENTIAL Excel Function
- SUMIF in Excel
- TAN Excel Function
- CEILING Excel Function
- LN Excel Function
- SIGN Excel Function
- COS Excel Function
- FLOOR Function in Excel
- SIN Excel Function
- Date and Time Function in Excel
- Statistical Function in Excel
SEARCH in Excel (Table of Contents)
SEARCH function in Excel
The Excel SEARCH function is text function which is used to find the location of a substring in a string/text.
The SEARCH function can be used as a worksheet function and it is not case sensitive function.
SEARCH Formula in Excel
Below is the SEARCH Formula in Excel
Explanation of SEARCH function in Excel
Excel SEARCH function has three parameter two (find_text, within_text) are compulsory parameters and one (start_num) is optional.
- find_text: find_text refers to the substring/character which you want to search within a string or the text you want to find out.
- within_text:. Where your substring is located or where you perform the find_text.
- [start_num]:: from where you want to start the SEARCH within_text. if omitted then SEARCH in Excel consider it as 1 and star search from first character.
How to Use SEARCH Function in excel?
Excel SEARCH function is very simple and easy to use. Let understand the working of SEARCH in excel by some examples.
SEARCH in Excel Example #1
Let’s perform a simple search of “Good” sub string in the given text or string. Here we have searched the Good word using Excel SEARCH function and it will return the location of Good word in the Good morning.
=SEARCH(“Good”,B6) and output will be 1.
And suppose there are two match found for good then SEARCH in excel will give you the first match value if you want the other good location then you just use the =SEARCH(“Good”,B7,2) [start_num] as 2 then it will give you the location of second match value and output will be 6.
SEARCH in Excel Example #2
In this example, we will filter out the first name and last name from the full name using the SEARCH in excel.
For first name=LEFT(B12,SEARCH(” “,B12)-1)
For Last name=RIGHT(B12,LEN(B12)-SEARCH(” “,B12))
SEARCH in Excel Example #3
Suppose there is set of IDs and you have to find out the _ location within IDs then use simply the Excel SEARCH to find out the “_” location within IDs.
=SEARCH(“_”,B27) and output will be 6.
SEARCH in Excel Example #4
Let’s understand the working of SEARCH in excel with wildcards characters.
Consider the give table and search for the text 0 within the text A1-001-ID
And start position will be 1 then =SEARCH(“?”&I8,J8,K8) output will be 3 because “?” neglect the one character before the 0 and output will be 3.
For the second row within given table the search result for A within B1-001-AY
Will be 8 but if we use “*” in search it will give you the 1 as location output because it will neglect all character before “A” and output will be 1 for it =SEARCH(“*”&I9,J9).
Similarly for “J” 8 for=SEARCH(I10,J10,K10) and 7 for =SEARCH(“?”&I10,J10,K10).
Similarly for fourth row the output will be 8 for =SEARCH(I11,J11,K11) and 1 for =SEARCH(“*”&I11,J11,K11)
Things to Remember About the SEARCH Function in Excel
- SEARCH in Excel is not case-sensitive
- It considers tanuj and Tanuj as a same value means it does not distinguish b/w lower case and upper case.
- SEARCH in Excel also allowed wildcard characters i.e. “?” ,“*” and “~” tilde.
- “?” is used to find a single character.
- “*” is used for match sequence.
- If you actually want to search the “*” or “?” then use the “~” before character.
- SEARCH returns the #VALUE! Error if there is no matching string is found in the within_text.
Suppose in the below example we are searching for a substring “a” within “Name” column if found it will return the location of a within name else it will give #VALUE error as shown below.
You can download this Search Function in Excel template here – Search Function Excel Template
This has been a guide to SEARCH Function in Excel. Here we discuss the SEARCH Formula in excel and how to use SEARCH function along with excel example and downloadable excel templates. You may also look at these useful functions in excel –