Returns the position of the first occurrence of find_text within within_text. Case-sensitive search function essential for text parsing, data extraction, and string manipulation in Excel.
Master the fundamentals of Excel FIND function
The FIND function is Excel's case-sensitive text search tool that returns the position of the first occurrence of find_text within within_text. It's essential for text parsing, data extraction, and string manipulation, particularly when case sensitivity matters.
Distinguishes between uppercase and lowercase
Returns numeric position of found text
Can specify starting position for search
Returns #VALUE! when text not found
Function-specific parameters
Function-specific return type
Extract data by finding delimiters
Find @ symbol to extract domains
Check if specific text exists
Combine with LEFT, RIGHT, MID for extraction
Exact matching required
Returns numeric position
Handles missing text gracefully
=FIND(find_text, within_text, start_num)The text to find (case-sensitive)
The text to search within
The position number to start searching from (default: 1)
Position number of first occurrence, or #VALUE! if not found
Description: Returns the position of the first occurrence of find_text within within_text. Case-sensitive.
Find position of text within string
Returns position 7 where 'World' starts
Simple VBA implementation of FIND function
' Basic FIND in VBA
Dim position As Long
position = Application.WorksheetFunction.Find("@", Range("A1").Value)
Range("B1").Value = position
' Using VBA FIND function
Dim text As String
text = "Hello World"
Dim pos As Long
pos = Application.WorksheetFunction.Find("World", text)
Debug.Print pos ' Output: 7
' Loop through range and find text
Sub FindTextInRange()
Dim cell As Range
Dim searchText As String
searchText = "@"
For Each cell In Range("A1:A10")
If Not IsEmpty(cell.Value) Then
On Error Resume Next
Dim pos As Long
pos = Application.WorksheetFunction.Find(searchText, cell.Value)
If Err.Number = 0 Then
cell.Offset(0, 1).Value = pos
Else
cell.Offset(0, 1).Value = "Not Found"
Err.Clear
End If
On Error GoTo 0
End If
Next cell
End SubComprehensive FIND implementation with error handling
' Function to find text with error handling
Function FindTextSafe(findText As String, withinText As String, Optional startNum As Long = 1) As Variant
On Error Resume Next
Dim pos As Long
pos = Application.WorksheetFunction.Find(findText, withinText, startNum)
If Err.Number = 0 Then
FindTextSafe = pos
Else
FindTextSafe = "Not Found"
Err.Clear
End If
On Error GoTo 0
End Function
' Extract email domain using FIND
Function ExtractDomain(email As String) As String
On Error Resume Next
Dim pos As Long
pos = Application.WorksheetFunction.Find("@", email)
If Err.Number = 0 And pos > 0 Then
ExtractDomain = Right(email, Len(email) - pos)
Else
ExtractDomain = "Invalid Email"
End If
On Error GoTo 0
End Function
' Usage example
Sub TestFindFunctions()
Range("B1").Value = FindTextSafe("@", Range("A1").Value)
Range("C1").Value = ExtractDomain(Range("A1").Value)
End SubExtract domain from email addresses
Parse text using delimiters
Check if text contains specific substring
Extract text after specific character
FIND returns #VALUE! when text is not found
=IFERROR(FIND("text", A1), "Not Found")Solution: Use IFERROR or ISNUMBER to handle errors: =IFERROR(FIND("text", A1), "Not Found") or =IF(ISNUMBER(FIND("text", A1)), "Found", "Not Found")
FIND is case-sensitive and may not find text
=SEARCH("text", A1)Solution: Use SEARCH for case-insensitive matching, or convert both to same case: =FIND(UPPER("text"), UPPER(A1))
start_num must be positive and within text length
=FIND("text", A1, MAX(1, MIN(start_num, LEN(A1))))Solution: Ensure start_num is between 1 and LEN(within_text). Use IF to validate: =IF(start_num>0 AND start_num<=LEN(A1), FIND(...), #VALUE!)
FIND with empty find_text may cause unexpected results
=IF(LEN("")>0, FIND("", A1), #VALUE!)Solution: Validate that find_text is not empty before using FIND: =IF(LEN(find_text)>0, FIND(find_text, A1), #VALUE!)