Return the position of one string within another, starting by default at the beginning of the string.
Syntax
Instr ( [start], FullString, PartString, [compare] )
Key
Start Number (expression) that sets the start position for each search.
Default = 1
FullString The String (expression) to be searched.
PartString The String (expression) being sought.
compare The comparison mode (Binary/Text)
The InStr() function can be used in VBA or in an SQL query.
Return values
If InStr() returns String is found Position at which match is found FullString is zero-length 0 FullString is shorter than start 0 FullString is Null Null PartString is zero-length start PartString is Null Null PartString is not found 0
Dim intDemo as Integer
intDemo = InStr("Talent is just a skill you're willing to practice", "is")
MsgBox intDemo
intDemo = InStr(4, "Talent is just a skill you're willing to practice", "e")
MsgBox intDemo
This will of course only find the first occurence, to replace every occurence on one string within another we need a recursive function like this:
Public Function FindAndReplace(ByVal strText As String, _
ByVal strFind As String, _
ByVal strReplaceWith As String) As String
'Purpose: Find and replace a string
'
'Use the faster left$, right$ functions rather than left() and Right().
'Trailing Spaces don’t get passed to this function so won’t be replaced.
On Error GoTo FindAndReplace_err
Dim lngPosition As Long ' string position
Dim strRemaining As String
If strText = "" Then GoTo FindAndReplace_exit 'no text = nothing to do
' find the first match.
lngPosition = InStr(1, strText, strFind)
If lngPosition <> 0 Then ' We have found at least one match
' Find what’s left of the original string:
' The length of the remaining string is the length of strText
' minus the match just found Len(strFind)
' and minus the area skipped (lngPosition)
' lngPosition is also the first char of strFind so add 1.
strRemaining = Right$(strText, Len(strText) - lngPosition - Len(strFind) + 1)
'Recursively FindAndReplace() what’s left of the original String
strRemaining = FindAndReplace(strRemaining, strFind, strReplaceWith)
'Do the replacement
strText = Left$(strText, lngPosition - 1) + strReplaceWith + strRemaining
End If
'Return the modified string
FindAndReplace = strText
FindAndReplace_exit:
Exit Function
FindAndReplace_err:
Msgbox Err.Number & Err.Description
Resume FindAndReplace_exit
End Function
“Believe those who are seeking the truth; doubt those who find it” ~ Andre Gide
InstrRev - Return the position of one string within another
Replace - Replace a sequence of characters in a string.