Excel FIND Function

The Excel FIND function returns the position of a specific sub-string within a string

Example: Excel FIND Function

Excel FIND Function

METHOD 1. Excel FIND Function using hardcoded values

EXCEL

=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.

METHOD 2. Excel FIND Function using links

EXCEL

=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

Formulas tab > Function Library group > Text > FIND > populate the input boxes

=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.
Built-in Excel FIND Function using hardocded values

METHOD 4. Excel FIND function using the Excel built-in function library with links

EXCEL

Formulas tab > Function Library group > Text > FIND > populate the input boxes

=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).
Built-in Excel FIND Function using links

METHOD 1. Excel FIND function using VBA with hardcoded values

VBA

Sub Excel_FIND_Function()
'declare a variable
Dim ws As Worksheet
Set ws = Worksheets("FIND")

'apply the Excel FIND function
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

OBJECTS
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.
PREREQUISITES
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.

METHOD 2. Excel FIND function using VBA with links

VBA

Sub Excel_FIND_Function()
'declare a variable
Dim ws As Worksheet
Set ws = Worksheets("FIND")

'apply the Excel FIND function
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

OBJECTS
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.
PREREQUISITES
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.

Usage of the Excel FIND function and formula syntax

EXPLANATION

DESCRIPTION
The Excel FIND function returns the position of a sub-string's first occurrence in a string.
SYNTAX
=FIND(find_text, within_text, [start_num])
ARGUMENTS
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.