Returns the position of the first occurrence of find_text within within_text. Case-insensitive search function essential for text parsing, data extraction, and flexible string manipulation in Excel.
Master the fundamentals of Excel SEARCH function
The SEARCH function is Excel's case-insensitive 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 flexible string manipulation, particularly when case sensitivity doesn't matter.
Matches text regardless of case
Returns numeric position of found text
Can specify starting position for search
Supports wildcards ? and *
Function-specific parameters
Function-specific return type
Search text without case sensitivity concerns
Find @ symbol to extract domains
Check if specific text exists (case-insensitive)
Combine with LEFT, RIGHT, MID for extraction
Exact matching required
Returns numeric position
Handles missing text gracefully
=SEARCH(find_text, within_text, start_num)The text to find (case-insensitive)
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-insensitive.
Find position of text within string (case-insensitive)
Returns position 7 where 'World' starts (case-insensitive match)
Simple VBA implementation of SEARCH function
' Basic SEARCH in VBA
Dim position As Long
position = Application.WorksheetFunction.Search("@", Range("A1").Value)
Range("B1").Value = position
' Using VBA SEARCH function
Dim text As String
text = "Hello World"
Dim pos As Long
pos = Application.WorksheetFunction.Search("world", text)
Debug.Print pos ' Output: 7 (case-insensitive)
' Loop through range and search text
Sub SearchTextInRange()
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.Search(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 SEARCH implementation with error handling
' Function to search text with error handling
Function SearchTextSafe(findText As String, withinText As String, Optional startNum As Long = 1) As Variant
On Error Resume Next
Dim pos As Long
pos = Application.WorksheetFunction.Search(findText, withinText, startNum)
If Err.Number = 0 Then
SearchTextSafe = pos
Else
SearchTextSafe = "Not Found"
Err.Clear
End If
On Error GoTo 0
End Function
' Extract email domain using SEARCH
Function ExtractDomain(email As String) As String
On Error Resume Next
Dim pos As Long
pos = Application.WorksheetFunction.Search("@", 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
' Search with wildcards
Function SearchWithWildcard(withinText As String, pattern As String) As Variant
On Error Resume Next
Dim pos As Long
pos = Application.WorksheetFunction.Search(pattern, withinText)
If Err.Number = 0 Then
SearchWithWildcard = pos
Else
SearchWithWildcard = "Not Found"
End If
On Error GoTo 0
End Function
' Usage example
Sub TestSearchFunctions()
Range("B1").Value = SearchTextSafe("@", Range("A1").Value)
Range("C1").Value = ExtractDomain(Range("A1").Value)
End SubExtract domain from email addresses
Parse text using delimiters (case-insensitive)
Check if text contains substring (case-insensitive)
Search using wildcards ? and *
SEARCH returns #VALUE! when text is not found
=IFERROR(SEARCH("text", A1), "Not Found")Solution: Use IFERROR or ISNUMBER to handle errors: =IFERROR(SEARCH("text", A1), "Not Found") or =IF(ISNUMBER(SEARCH("text", A1)), "Found", "Not Found")
SEARCH treats ? and * as wildcards, not literal characters
=SEARCH("~?", A1)Solution: Use ~ before wildcard to search for literal character: =SEARCH("~?", A1) to find question mark, or use FIND for exact matching
start_num must be positive and within text length
=SEARCH("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), SEARCH(...), #VALUE!)
When case sensitivity is required, SEARCH may not be appropriate
=FIND("text", A1)Solution: Use FIND for case-sensitive searches, or convert both to same case for SEARCH: =SEARCH(UPPER("text"), UPPER(A1))