Excel FIND Function
The Excel FIND function returns the position of a specific sub-string within a string
Example: Excel FIND Function
|
=FIND("an",B5)
|
Result in cell E5 (2) - returns the position of "an" 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.
|
|
=FIND("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.
|
|
=FIND("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.
|
|
=FIND(C5,B5)
|
Result in cell E5 (2) - returns the position of the text in cell (C5), which is "an", 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.
|
|
=FIND(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).
|
|
=FIND(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 FIND function using the Excel built-in function library with hardcoded values
EXCEL
| =FIND("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 FIND function using the Excel built-in function library with links
EXCEL
| =FIND(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.Find("an", ws.Range("B5"))
ws.Range("E6") = Application.WorksheetFunction.Find("na", ws.Range("B6"), 1)
ws.Range("E7") = Application.WorksheetFunction.Find("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 FIND.
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.Find(ws.Range("C5"), ws.Range("B5"))
ws.Range("E6") = Application.WorksheetFunction.Find(ws.Range("C6"), ws.Range("B6"), ws.Range("D6"))
ws.Range("E7") = Application.WorksheetFunction.Find(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 FIND.
String Range: Have the range of strings that you want to search within captured in range ("B4:B7").
Find 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.
Find 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 FIND function returns the position of a sub-string's first occurrence in a string.
=FIND(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 FIND function is case sensitive.
Note 2: If the function cannot locate the sub-string in the specified string it will return a #VALUE error.
Note 3: The FIND function does not support wildcards.