-
Feb 25th, 2021, 05:27 PM
#1
Thread Starter
Junior Member
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)
-
Feb 26th, 2021, 03:43 AM
#2
Re: Copy Paste (Capture) an action word into empty cell
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
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Feb 26th, 2021, 11:15 AM
#3
Thread Starter
Junior Member
Re: Copy Paste (Capture) an action word into empty cell
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
-
Feb 26th, 2021, 11:36 AM
#4
Re: Copy Paste (Capture) an action word into empty cell
It works, but the thrid parameter of the mid function needs some tweaking...
I almost had it working...
-tg
-
Feb 26th, 2021, 03:42 PM
#5
Re: Copy Paste (Capture) an action word into empty cell
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
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Feb 27th, 2021, 01:39 PM
#6
Thread Starter
Junior Member
Re: Copy Paste (Capture) an action word into empty cell
How can I set it up to run it as a Sub?
-
Feb 27th, 2021, 03:59 PM
#7
Re: Copy Paste (Capture) an action word into empty cell
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
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|