Text in column D - Search for keywords, replace with GAP and place keyword in column E

I have a text. I also have an array of keywords that appear in the that text.
I need to search for those key words and when a match is found:
a - Replace that keyword with the text GAP
b - The keyword that was found will be placed in column E.

The keywords must be in order that they occur in text.


Text
Yesterday, I went to Paul's house because he was ill. His mum was angry with me because I ate all the food in the fridge and I drank all the milk. I drove my car to the supermarket and I bought some bread and cheese and I took it back to the house. Paul's mum was happy when she saw the food.

Words or expressions array
went, was, ate, drank, drove, bought




What I need to generate is

COLUMN D
Yesterday, I GAP to Paul's house because he GAP ill. His mum was angry with me because I GAP etc...
Then those words need to appear in COLUMN E like so:
went
was
ate
drank
drove
bought

Thanks

Oh wow. Yes, please.

Here you go

Please Login or Register  to view this content.

Try this one instead, should work on a mac too.

Please Login or Register  to view this content.

I tried uploading the excel. Let's ee if you get it.

JEC - Hi - thanks but I downloaded the excel and it didn't work.

Yes you have to replace the code in the file with the second code I gave you

Cannot run the macro "Map1.xlsm!jec". The macro may not be available in this workbook or all macros may be disabled.

I get this error. But macros are enabled.

The button is still assigned to the “old” macro. Reassign the new macro to the button or run it from the VBA editor.

Hi I have it working now but it is missing some functionality that I was looking for.
Right now you have the array of answers in column E in order and a list BUT when I start I have the words in column C in unordered.
I need the program to iterate through my "answers" in column C - match them in the text - replace with GAP and place them as the ordered answers in column E.

It doesn't sound like much but if I have 30 answers then it is a pain and my always make mistakes - overlook a word now and again.

Hi guys, I'm sure I'm doing something wrong. I get "Active X component can't create object".
Is there any chance of you pasting this into the excel I uploaded. I seem to be missing a step.

Hi JEC - that generated went and was only in column E. The rest were not generated and still in the text.

Let me close everything - redownload and test again.

see the attached.

Jindon - this one worked for me.
Thank you so much to Jindon, JEC and Popipipo.

This will save me so much work. I think you're all great.

Do I click anywhere to show my appreciation. I can't see any "click right answer" like in stackoverflow.

You are welcome.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

Oh - question. It works for the first text. I tried another text just below and clicked the run button but it does not work for the send text. I may have over a thousand texts here in the future. How do I apply the macro to the second text.

Can you just upload a few example and the result that you want?

I think I'm going crazy - I can't find the upload icon.

Please read the yellow banner at the top of this page on how to attach a file and a mocked up solution.

I hope I have enclosed the file.

Assuming you have enough gap between data.

Please Login or Register  to view this content.

I copied in and pasted the new code into the VBA module.
I press the run button but only the first text and answers are generated.

See the attached.

Amazing. Thank you.

Ah - Ok.
I just changed code and noticed something else.

It finds "ever" in everything. So we would need to tell the code that any word found needs to have a space before and after it ie: it's not "within" another string.

In that case...

Please Login or Register  to view this content.

This is Case Sensitive, if you want it to non case sensitive then change 0 to 1

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

That's amazing but now I noticed that it finds each word multiple times. It must only find the first occurence then stop searching. Sorry!!!

So only first appeared match?

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

That is brilliant. I'm really sorry that I wasn't as specific as I should have been. I will really try and be more detailed in my requirements next time.
Thanks - it helped so much. That code is way over my head, but when I read it, I am learning all the time.