Macro to check for words in a given paragraph !

Hi,

I wanted a formula or VBA code which finds few pre-listed words from a paragraph and gets the result in a cell. plz check the example below

List of words: David, peter, rose, maria, ram
Paragraph: David went to peter's place to borrow a CD rented from maria's shop.

resulting Cell : David, peter, maria

Please Help !

Hello Steven,

Thank you very much for your help ! I appriciate it ! Answering your questions.

Paragraph: I was refering to a paragraph in a cell. I think the right way to put it across is as you said sentence.

where is you list of words going to be?
The words will be in a reference sheet (which is not the sheet where the sentence is there) it is fine if the result is show in reference sheet or the sheet in which the sentence is located.

Yes, I want it case sensitive.

Steven, it would be very helpful if you include a comment with codes and explain me wat it does.

thank you so much steven.

cheers,

kinirobin

It would help me if you would create and upload a mock up of your workbook with examples in one worksheet and the lists in another worksheet, or however you want it set up.

Hi Steven,

Sorry for the late reply. I have attached a Moc sheet with this post. It would be great if you can give me a solution which runs solely on the basis of a Macro rather than inputting a formula(function based code). If its not possible....I am fine with the formula itself. It would be great if the Macro which is case sensitive.

Thank you very much steven.

Rgrds

Kinirobin

Also, UDF

=CountWords(B2,List!A1:A5,TRUE)
True for case-sensitive, False for not case-sensitive.

Please Login or Register  to view this content.

Hi Jindon,

Thanks a lot for the swift reply! I was looking for a code which gives me the words(mentioned in the list sheet) in the
result column rather than numbers. like - wilson,renowned,novel,James goldsberg and so on.

Many Thanks,
Kinirobin

---------- Post added at 09:44 AM ---------- Previous post was at 09:38 AM ----------

Hi Jindon,

Steven's code works fantastically (plz refer to steven's posts). But its not case sensitive. If this can be fixed nothing like it. more over is it possible to alter the macro in such a way where in only the code does all the things rather we entering the formula in the sheet for the code to work.

Many Thanks,
Kinirobin

OK,
Change to

Please Login or Register  to view this content.

Hi Jindon,

Regarding the code I found one thing that for example if we are trying to find 'Us'word in a sentence and if the sentence contains a name called Justin or agustine the code will retrieve the 'us' part from the name. can the code get theexact match if 'Us' is present in the sentence only that should be recognized not letters matching to it. Can this be done jindon.
It would be fantastic if there is a solution for it. awaiting your response.

Many Thanks,
Kinirobin

Change to

Please Login or Register  to view this content.

Cool !!!!........its working !!!

Thank you !

Hey Jindon,

Hope all is well. I have one more requirement which is kind of similar to this post. So thought of not to start a new post. the requirement is something like this.

Say we have a cell called gender - it can be populated as Male or Female(or M , F). and you have a write up(a sentence) in another cell about a person which will have gender defining words like he, his, him,Mr. can we create a VBA code which checks for these words (he, his, him)and do a comparison with the gender cell. for example, if the gender cell has M and the write up has she,her (which is the opposite *** words)the code should throw an error saying gender not correct.

Can we do that?

Thanks in Advance,
Kinirobin

I think you need to open a new thread.

@ kinirobin

Before starting your new thread:

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a new member of the forum, 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.

Hey cutter, thanks a lot for letting me know the rules. It's very kind of you. I would like to say that this particular forum is fantastic and full of people who are ready to help others and special Jindon has been very helpful and I would surely do the rating thing! Cheers excel forum!