Resources

    All about Visual Basic for Application (VBA) especially for Microsoft Excel.

Subscribe

  • Subscribe

how to find text in string in vba

Posted by | 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.

Share Button
Share on Facebook
Bookmark this on Digg
Bookmark this on Yahoo Bookmark
Bookmark this on Google Bookmarks
Bookmark this on Delicious

Leave a Comment

If you would like to make a comment, please fill out the form below.

Name (required)

Email (required)

Website

Comments