Search for string, then cut and paste the string to a new cell

[robbyvegas]

Hello again everyone!

I have inherited an Excel spreadsheet that contains information in Column A cells that I'd like to search for and then cut it from the rest of the string and paste it to another cell. Here's an example spreadsheet for the sake of context:

Example.xlsx

I need to write a formula/macro/script that searches through each cell in column A for a specified term ( e.g. "expert report" or "deposition exhibit"), and that then cuts the text in the string from the preceding "(" through the next ")". The cut text would then be pasted into the same row in column D. So if I ran the macro on the above table searching for the term "expert report" the resulting table should look like this:

Example 2.xlsx

I'm familiar with programming generally, but not specifically with macros and VBA in Excel. Can anyone help me out on this?

THANK YOU!!!
Rob

[JapanDave]

The rules change with each cells information. If you were taking data from in front of the key word or after the key word you could do this this , but you are trying to take different length strings (inclusive) in front of the key word. I honestly don't see how you can do this?

[smuzoen]

If the data was always in the format you present you could use the following code but I share the previous sentiments in that this may not be robust enough as the rules may not always apply. Do not run this on real data as you cannot undo VBA. I tried results with "expert report" and "deposition exhibit" (you do not have to enter the words in upper/lower case - it does not matter) and it outputs correctly. See attached workbook - there is copy of your results and a spare copy raw data for you to test it. Essentially the search terms would have to be within parentheses.

Please Login or Register  to view this content.

[robbyvegas]

[QUOTE=smuzoen;2834857] Essentially the search terms would have to be within parentheses.
[code]

Thank you both! Fortunately, the search string does always appear within parenthesis. I'll give this a shot and let you know how it turns out.

THANK YOU AGAIN!
Rob

[robbyvegas]

Works perfectly. I can't thank you enough! I have a follow-up question... How is the script determining where to paste the copied string? I'd like to be able to change the column where it is pasting if I need to.

Thanks again!

[smuzoen]

The code is looking in Column A for the string to examine. In this case cells(row, column) - so 1 in this case is Column A

Please Login or Register  to view this content.

So it then pastes the results into Column 1 and into Column 4 (Column D)

Please Login or Register  to view this content.

So just change the 4 to whatever column you want the results added to. Substitute 1 for the column with the data to search and 4 for the column where you want the extracted results to go.
Just one thing to add is if there is a bit of data to process add the following to stop screen flicker.

Please Login or Register  to view this content.

I hope that answers your question.

[robbyvegas]

Absolutely incredible. Thank you, you have saved me hours of manual cut and paste work.

On a completely different note, can you recommend a really good book on Excel? I'm interested in having a reference nearby when I need to figure out formulas, and I'd like to become proficient in scripting.

Thanks again!
Rob

[AB33]

http://www.excelforum.com/excel-prog...materials.html

[smuzoen]

I must admit I have a few e-Books but I rarely use them - I am more inclined to work on the premise "Google is Your Friend". If you have a question chances are the question has been asked many time before. The forum here can be searched and has some of the best Excel Gurus you could hope to find. However here are some links that I would recommend.
There is a Sticky on the front page of this forum http://www.excelforum.com/excel-prog...materials.html