Excel SEARCH Function
The Excel SEARCH function returns the position of a specific sub-string within a string
Example: Excel SEARCH Function
|
=SEARCH("ban",B5)
|
Result in cell E5 (1) - returns the position of "ban" from the specified text (Banana). Given that the start position was omitted, it default to 1, meaning that the search will begin from the first character of the specified text. The SEARCH function is not case-sensitive, therefore, it will return the position of "Ban" from the word "Banana" even though we have specified the text in lower case.
|
|
=SEARCH("na",B6,1)
|
Result in cell E6 (3) - returns the position of "na" from the specified text (Banana), with the search beginning from the first character of the specified text.
|
|
=SEARCH("na",B7,4)
|
Result in cell E7 (5) - returns the position of "na" from the specified text (Banana), with the search beginning from the fourth character of the specified text.
|
|
=SEARCH(C5,B5)
|
Result in cell E5 (1) - returns the position of the text in cell (C5), which is "ban", from the specified text (Bananas). Given that the start position was omitted, it default to 1, meaning that the search will begin from the first character of the specified text. The SEARCH function is not case-sensitive, therefore, it will return the position of "Ban" from the word "Banana" even though we have specified the text in lower case.
|
|
=SEARCH(C6,B6,D6)
|
Result in cell E6 (3) - returns the position of the text in cell (C6), which is "na", from the specified text (Bananas), with the search beginning from the first character of the specified text, which is represented by the value in cell (D6).
|
|
=SEARH(C7,B7,D7)
|
Result in cell E7 (5) - returns the position of the text in cell (C7), which is "na", from the specified text (Bananas), with the search beginning from the fourth character of the specified text, which is represented by the value in cell (D7).
|
METHOD 3. Excel SEARCH function using the Excel built-in function library with hardcoded values
EXCEL
| =SEARCH("na",B7,4) Note: in this example we are searching for the position of "na" in the specified text (Bananas), beginning the search from the fourth character. |
 |
METHOD 4. Excel SEARCH function using the Excel built-in function library with links
EXCEL
| =SEARCH(C7,B7,D7) Note: in this example we searching for the position of the text in cell (C7), which is "na", from the specified text (Bananas), cell (B7), with the search beginning from the fourth character of the specified text, which is represented by the value in cell (D7). |
 |
Dim ws As Worksheet
ws.Range("E5") = Application.WorksheetFunction.Search("ban", ws.Range("B5"))
ws.Range("E6") = Application.WorksheetFunction.Search("na", ws.Range("B6"), 1)
ws.Range("E7") = Application.WorksheetFunction.Search("na", ws.Range("B7"), 4)
End Sub
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
Worksheet Name: Have a worksheet named SEARCH.
String Range: Have the range of strings that you want to search within captured in range ("B4:B7").
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell references ("E5"), ("E6") and ("E7") in the VBA code to any cell in the worksheet, that doesn't conflict with formula.
String Range: Select the range of strings that you want to search within by changing the range ("B5:B7") in the VBA code to any cell in the worksheet, that doesn't conflict with formula.
Dim ws As Worksheet
ws.Range("E5") = Application.WorksheetFunction.Search(ws.Range("C5"), ws.Range("B5"))
ws.Range("E6") = Application.WorksheetFunction.Search(ws.Range("C6"), ws.Range("B6"), ws.Range("D6"))
ws.Range("E7") = Application.WorksheetFunction.Search(ws.Range("C7"), ws.Range("B7"), ws.Range("D7"))
End Sub
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
Worksheet Name: Have a worksheet named SEARCH.
String Range: Have the range of strings that you want to search within captured in range ("B4:B7").
Search sub-string: Have the sub-string that you want to search for in range ("C5:C7").
Start Position: In this example the start position only applies to the bottom two examples. Therefore, if using the exact same VBA code ensure that the start position value is captured in range ("D6:D7").
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell references ("E5"), ("E6") and ("E7") in the VBA code to any cell in the worksheet, that doesn't conflict with formula.
String Range: Select the range of strings that you want to search within by changing the range ("B5:B7") in the VBA code to any cell in the worksheet, that doesn't conflict with formula.
Search sub-string: Select the sub-string that you want to search for by changing the cell references ("C5"), ("C6") and ("C7") to any range in the worksheet, that doesn't conflict with the formula.
Start Position: Select the start position value by changing the range ("D6:D7") to any range in the worksheet, that doesn't conflict with the formula.
The Excel SEARCH function returns the position of a sub-string first occurrence a string.
=SEARCH(find_text, within_text, [start_num])
find_text: (Required) The sub-string that you want to find.
within_text: (Required) The string that you want to search within.
start_num: (Optional) The position in the within_text from where the function will begin its search.
ADDITIONAL NOTES
Note 1: The SEARCH function is case insensitive.
Note 2: If the function cannot locate the sub-string in the specified string it will return a #VALUE error.
Note 3: The SEARCH function supports wildcards.