vba code to extract 3 words before and 3 words after a certain word and create a new column with these 6 words in it for every row

Question

I am looking for a vba code to extract 3 words before and 3 words after a certain word. eg: C6 contains "Blah blah.These five red apples are so good blah blah". So here "apples" is the certain word. So the new column should have "These five red apples are so good". If there is something like "Blah Blah.These apples are so good blah blah". Then it should only extract "These apples are so good" anything before or after period should not be considered. If possible any extension of apple should be considerd as the certain word.I have seen some codes in python but is this possible in vba code and possible vba code. Any help on this is greatly appreciated.

How do you extract 3 words before and 3 after and arrive at 5 total XD — findwindow, Oct 05 '15 at 15:16
That's nice. Good luck finding that. Did you have a programming question? This site is for programming questions, not someplace to dump your to-do lists, or expect someone else do your job for you. — Marc B, Oct 05 '15 at 15:18
@ John Coleman thanks for the starting point will start with this, Since I am new to vba code,thats the reason why I did not add a code in the first place. But try along these lines and will update the code as the starting point or possibly the complete code — viji, Oct 05 '15 at 15:27
I can answer your question - yes, VBA is excellent at string manipulation. However, it seems as if you're asking for a tailor made solution, rather than help with a half-complete bit of your own code. Just asking for an entire solution to your problem (which probably has commercial value to *you*) is more appropriate to PeoplePerHour.com, eLance.com etc. — EyePeaSea, Oct 05 '15 at 17:00

score 0 · Accepted Answer · edited May 23 '17 at 11:59

Seems like a valid regex application and question for SO (though it could use a re-write and show more of things tried). Give this a try. Starting with text in column A like this:

Add reference to your project for "Microsoft VBScript Regular Expressions 5.5"

Then run this code:

Private Sub splitUpRegexPattern()
    Dim regEx As New RegExp
    Dim strPattern As String
    Dim Myrange As Range

    Set Myrange = ActiveSheet.Range("A1:A3")

    For Each c In Myrange
        strPattern = "(?:\w+\W+){3}(apples)\W+(?:\w+\W+){3}"

        If strPattern <> "" Then
            With regEx
                .Global = True
                .MultiLine = True
                .IgnoreCase = False
                .Pattern = strPattern
            End With

            Set matches = regEx.Execute(c.Value)
            On Error Resume Next
            c.Offset(0, 1).Value = matches.Item(0)
        End If
    Next c
End Sub

Results:

Need help with Regex in VBA? Check out this post to get started.

This would only capture first ocurrence how do I change the code to show multiple occurences seperated by "||" — viji, Apr 08 '16 at 20:16

vba code to extract 3 words before and 3 words after a certain word and create a new column with these 6 words in it for every row

1 Answers1