Return the position of one string within another, starting by default at the beginning of the string.

      Instr ( [start], FullString, PartString, [compare] )

   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("The Universe is everlasting ", "is")
MsgBox intDemo

intDemo = InStr(4, "The Universe is everlasting", "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
    Exit Function
    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.

Copyright © 1999-2021
Some rights reserved