How to return one and two words before and after a specific word

[MrsQ]

Hello,

I am in need of a forumula. I am trying to extract one word before a keyword, one word after a keyword, 2 words before a keyword, and 2 words after a keyword.

For example:

The keyword is blue:


birds are blue today - 1 word before=are; 1 word after=today; 2 words before=birds are; 2 words after=today

blue is my favorite color - 1 word before=(null); 1 word after=is; 2 words before=(null); 2 words after=is my

see blue colors in the sky - 1 word before=see; 1 word after=colors; 2 words before=see; 2 words after=colors in


Any help would be very much appreciated!!

 

[smiler]

hi,

add this code to module in VBA

Function getwords(sentence As String, keyword As String, opt As String)
getwords = ""
wrd = 0
x = Split(sentence, " ")
For Each word In x
   If UCase(word) = UCase(keyword) Then
      Select Case opt
          Case "1B"
             If wrd = 0 Then
                getwords = ""
                Exit Function
             Else
                getwords = x(wrd - 1)
                Exit Function
             End If
          Case "2B"
             If wrd < 2 Then
                getwords = ""
                Exit Function
             Else
                getwords = x(wrd - 2)
                Exit Function
             End If
          Case "1A"
             If wrd + 1 > UBound(x) Then
                getwords = ""
                Exit Function
             Else
                getwords = x(wrd + 1)
             End If
          Case "2A"
             If wrd + 2 > UBound(x) Then
                getwords = ""
             Else
                getwords = x(wrd + 2)
             End If
      End Select
   End If
   wrd = wrd + 1
Next
End Function

you then have a new function called getwords

Useage:

getwords(Sentence,required word,answer type)

Answer types:

"1B" - 1 Before
"2B" - 2 Before
"1A" - 1 After
"2A" - 2 After

Example below

Excel Workbook
	G	H	I	J	K
7	Keyword	blue
8	Sentence	1 Before	2 Before	1 After	2 After
9	birds are blue today	are	birds	today
10	blue is my favorite color			is	my
11	see blue colors in the sky	see		colors	in
12	i like the colour blue	colour	the
Sheet7

 

[MrsQ]

WOW! That is incredible!

One question:

Is it possible for the 2B and 2A to actually contain 2 words OR just 1 word if there is only one word before/after blue?

For example:
in row 9, 2B=birds are (instead of birds); 2A=today
in row 10, 2A=is my (instead of is)
in row 11, 2B=see (instead of null); 2A=colors in (instead of in)
in row 12, 2B=the colour (instead of the)


I can always do a formula to concatenate what's already there but I thought I'd chek.

Thank you again!!!!! You are amazing!

 

[smiler]

Hi,

replace previous function with this one

Function getwords(sentence As String, keyword As String, opt As String)
getwords = ""
wrd = 0
x = Split(sentence, " ")
For Each word In x
   If UCase(word) = UCase(keyword) Then
      Select Case opt
          Case "1B"
             If wrd = 0 Then
                getwords = ""
                Exit Function
             Else
                getwords = x(wrd - 1)
                Exit Function
             End If
          Case "2B"
             If wrd < 2 Then
                getwords = ""
                Exit Function
             Else
                getwords = x(wrd - 2) & " " & x(wrd - 1)
                Exit Function
             End If
          Case "1A"
             If wrd + 1 > UBound(x) Then
                getwords = ""
                Exit Function
             Else
                getwords = x(wrd + 1)
             End If
          Case "2A"
             If wrd + 2 > UBound(x) Then
                getwords = ""
             Else
                getwords = x(wrd + 1) & " " & x(wrd + 2)
             End If
      End Select
   End If
   wrd = wrd + 1
Next
End Function

 

[smiler]

Hi - Sorry i missed something - the previous new version did add 2 words, but didnt add 1 word if only 1 word was available, this version fixes that so should work exactly as requested

replace function with this function - formlas remain the same

Function getwords(sentence As String, keyword As String, opt As String)
getwords = ""
wrd = 0
x = Split(sentence, " ")
For Each word In x
   If UCase(word) = UCase(keyword) Then
      Select Case opt
          Case "1B"
             If wrd = 0 Then
                getwords = ""
                Exit Function
             Else
                getwords = x(wrd - 1)
                Exit Function
             End If
          Case "2B"
             If wrd < 2 Then
                If wrd = 1 Then
                   getwords = x(wrd - 1)
                End If
                Exit Function
             Else
                getwords = x(wrd - 2) & " " & x(wrd - 1)
                Exit Function
             End If
          Case "1A"
             If wrd + 1 > UBound(x) Then
                getwords = ""
                Exit Function
             Else
                getwords = x(wrd + 1)
             End If
          Case "2A"
             If wrd + 2 > UBound(x) Then
                If wrd + 1 <= UBound(x) Then
                   getwords = x(wrd + 1)
                End If
             Else
                getwords = x(wrd + 1) & " " & x(wrd + 2)
             End If
      End Select
   End If
   wrd = wrd + 1
Next
End Function

 

[MrsQ]

Beautiful! Thank you so much for your help!!

 

[VeeK727]

Hey, it doesn't work for me. Here's the sample data

Dr. Arun Kumar Arunosha Labs Pvt Ltd 203/4 Local Shopping Centre Gujranwala Town, Part I Delhi – 110 009 <tbody> </tbody>	=GetWords(A1,$B$13,"2A")
Dr. Rajiv Anand D/222, Defence Colony , Opp Lajpat nagar Metro Stn New Delhi – 110 024 <tbody> </tbody>	=GetWords(A2,$B$13,"2A")
Dr. Sitara Kahai E-43, Panchsheel Park New Delhi – 110 017 <tbody> </tbody>	=GetWords(A3,$B$13,"2A")

<tbody>
</tbody>

B13 has this text: "Dr. " (D R PERIOD SPACE)

And this is the code of the module:

Function getwords(sentence As String, keyword As String, opt As String)
getwords = ""
wrd = 0
x = Split(sentence, " ")
For Each word In x
   If UCase(word) = UCase(keyword) Then
      Select Case opt
          Case "1B"
             If wrd = 0 Then
                getwords = ""
                Exit Function
             Else
                getwords = x(wrd - 1)
                Exit Function
             End If
          Case "2B"
             If wrd < 2 Then
                If wrd = 1 Then
                   getwords = x(wrd - 1)
                End If
                Exit Function
             Else
                getwords = x(wrd - 2) & " " & x(wrd - 1)
                Exit Function
             End If
          Case "1A"
             If wrd + 1 > UBound(x) Then
                getwords = ""
                Exit Function
             Else
                getwords = x(wrd + 1)
             End If
          Case "2A"
             If wrd + 2 > UBound(x) Then
                If wrd + 1 <= UBound(x) Then
                   getwords = x(wrd + 1)
                End If
             Else
                getwords = x(wrd + 1) & " " & x(wrd + 2)
             End If
      End Select
   End If
   wrd = wrd + 1
Next
End Function

The idea is to get names out of the data, which are two words after "Dr. ". What's wrong here?

EDIT: The result that I get is "#NAME?" and the error is "The formula contains unrecognized text"