how to find text in string in vba

Posted by hisham hadi | July 20, 2011 .

InStr in VBA (Visual Basic for Application)
When we work with phrase or string some time we wanted to search for specific word or character whether exist or not and sometime we wanted to know exact location for the word or characters. Here we can use function InStr( Start point, Your Phrase, “Your Text”) for example “Money” position in “Make money online”

Option Explicit
Sub FindText()
Dim FndMoney As Byte
Dim PhraseTxt As String

    PhraseTxt = "Make Money Online" 'You can change this refer to cell ex: Range("A1")
    FndMoney = InStr(1, PhraseTxt, "Money")
    MsgBox (FndMoney)
End Sub

I put start point = 1 because we want to start from the beginning, if you try the above macro or code the answer given is equal to 6. This is because “Money” position is started from 6 to 10 includes space inside “Make money online”. 1 = M, 2 = a, 3 = k, 4 = e, 5 = space then no 6 where M for money begin. Hope you can understand the concept.
Therefore if you wish to the check the condition whether exist or not then we can add FndMoney not equal to zero or less than 1 before proceed as below code:

Option Explicit
Sub FindText()
Dim FndMoney As Byte
Dim PhraseTxt As String

    PhraseTxt = "Make Money Online" 'You can change this refer to cell ex: Range("A1")
    FndMoney = InStr(1, PhraseTxt, "Money")
    If FndMoney <> 0 Then
        MsgBox ("The position of Money in " & """" & PhraseTxt & """" & " = " & FndMoney)
    Else
        MsgBox ("Sorry Money doesn't exist in " & """" & PhraseTxt & """")
    End If
End Sub

If you try the above code then the answer given = ” The position of Money in “Make Money Online” = 6”. Let try to change the PhraseTxt = Make Friend Online then the answer given = “Sorry Money doesn’t exist in Make Friend Online”.
This function InStr(Start, String searched, “String search for”) is very powerful in VBA programming for example we can use in data processing automatically return something when we found specific keywords and number of keywords is unlimited.
Enjoy your learning.