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