VBA: Search an API response for any of 10 words. If word is found, display the 20 characters before & after

[yits05]

Hello,

I have reached my VBA abilities in trying to figure this out, and was hoping for some help. I have a script which calls an API and returns a bunch of text, stored in a string variable. I was wondering if there is any way in which I can VBA search the string for any of a list of keywords, and if one is found, for it to return in a cell the 20 characters before & after the keyword appears?

There is also a chance that multiple keywords will appear in the string, so I would want them added vertically in the same row, ie:

hsfdhfosjglksjfgKEYWORD1hgwlkejtghljkhwetlg

ewjf'lkwqg]pojqj34pi5jgKEYWORD2hjfdk.hwrlhjwlghjqkdwjg

...

Much appreciated!

 

[John_w]

Try this - implemented as a test routine and a search routine which you can call from your code.

Public Sub Test()

    Dim APItext As String
    Dim keywords As Variant
    
    APItext = "AAAAAAhsfdhfosjglksjfgKEYWORD1hgwlkejtghljkhwetlgBBBBBCCCCCCewjflkwqg]pojqj34pi5jgKEYWORD2hjfdk.hwrlhjwlghjqkdwjgDDDDDD"
    
    keywords = Array("KEYWORD1", "KEYWORD2", "KEYWORD3")
    
    Keywords_Search APItext, keywords, Range("A2")
        
End Sub


Public Sub Keywords_Search(APItext As String, keywords As Variant, destCell As Range)

    Dim keyword As Variant
    Dim p1 As Long, p2 As Long
    Dim colOffset As Long
    
    colOffset = 0
    For Each keyword In keywords
        p2 = InStr(1, APItext, keyword, vbBinaryCompare)
        If p2 > 0 Then
            p1 = p2 - 20
            If p1 < 1 Then p1 = 1
            destCell.Offset(, colOffset).Value = Mid(APItext, p1, p2 - p1 + Len(keyword) + 20)
            colOffset = colOffset + 1
        End If
    Next
    
End Sub