Copy Paste (Capture) an action word into empty cell

Working w/ contracting/quotes, etc. "CONTRACTOR SHALL" statements and want to be able locate the word that follows the statement "Contractor Shall" in every line then copy/paste that single word into either Col A or C (whichever is easiest programmatically).. My example shows it in Col A.

So, when it's done, it will look like the attached image: (and like statements can be grouped)

Attachment 180326

looking at your example as best as i can see, your line 2 does not follow the requirement

you can copy this formula into c2 and drag it down the column as far as you need
=MID(C2,FIND("contractor shall ",C2,1)+ LEN("contractor shall "),FIND(" ",C2,FIND("contractor shall ",C2,1)+1 -FIND("contractor shall ",C2,1))-1)
test to see if it does as you require

Westconn1, thank you - yes, you are correct, line 2 was not updated properly -- it should reflect the word "provide". (just like all the examples below it, --- it should be picking up the full word)

When I pasted in your solution to test it -- it returns "pro" (but not the full word that follows "Contractor Shall")...
Looks like it's limited to only 3 characters if I drag it downward..
That's why I was thinking VBA might be necessary to make it pick up the word (as the number of characters varies all the way down with different action verbs).. There's a way to make it pick up everything up until a blank space occurs (that way it captures the full word no matter what the length of that word is)... once the vba gets to an empty space (non character) it knows to stop...

provide
conduct
establish
coordinate
negotiate
identify
build

etc

It works, but the thrid parameter of the mid function needs some tweaking...

I almost had it working...

-tg

Quote:

---

There's a way to make it pick up everything up until a blank space occurs

---

that is what it is supposed to do, it worked with a sample i tried, i will look at it further

of course with vba it would be much simpler to code but not as efficient as using in-built functions in a formula

here is a function that appears to work correctly on several samples, the original formula should have been doing the same thing, but on a single line without the help of variables

Code:

---

Function getnextword(s As String) As String
Dim pos As Integer, pos1 As Integer
pos = InStr(s, "contractor shall ") + Len("contractor shall ")
pos1 = InStr(pos + 1, s, " ")
If pos1 - pos > 1 Then
    getnextword = Mid(s, pos, pos1 - pos)
    Else
    getnextword = Mid(s, pos) ' this is case the next word is the last word in the cell, so no space to find
End If

---

call like =getnextword(c2)

i will look at the original formula again later

How can I set it up to run it as a Sub?

this sub will use the function above, or it could all be incorporated together

Code:

---

Sub doall()
rw = 2
Do
    If IsEmpty(Cells(rw, 3)) Then Exit Do
    Cells(rw, 1) = getnextword(Cells(rw, 3))
    rw = rw + 1
Loop
End Sub

---

processes all rows until an empty cell in column C