How to find batch of words in Excel ?

Hi Expert, I have an Excel table. If I try to find one word or digit in the table, it would be easy to find it through the finding function. but if I try to find ten words or more at one time, how can I do it ? Thank you !
eemoonAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
If you can, please supply a sample workbook.

What do you mean when you say "if I try to find ten words or more at one time"? Do you mean you want to find a string of words like this "We the people in order to form a more perfect union" in a single cell, or do you want to find "We" and "the" and "people", etc. even if they are in separate cells?
Saurabh Singh TeotiaCommented:
Martin,

You probably want to look at this one as this a duplicate question..same what the user posted in this one...

www.experts-exchange.com/Q_28678578.html

I don't see any changes in both apart then in this he specified excel and in that one he said excel and word...

Saurabh...
eemoonAuthor Commented:
Hi Martin, Thank you so much for your fast reply. I am giving an example to explain: In Excel table, there are many data, such as a1, a2, a3, a4, a5, a6, a7 ... in each cell. If i want find three data, a1, a2, and a3, I need to use Find function of Excel three times to find them. How can I find the three data one time ?
Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

eemoonAuthor Commented:
saurabhh are you in college ?
Martin LissOlder than dirtCommented:
The attached workbook looks for "this", "is" and "test". Run the FindAll macro.
Q-28678669.xlsm
eemoonAuthor Commented:
Hi Martin, Thank you so much for your suggestion. I think it would work for my purpose. Now I do not know how to use it. Can you send me link that can explain it further ? I have not found where the Run the FindAll macro is yet
Martin LissOlder than dirtCommented:
I'm not at home right now. I will answer in the morning.
Martin LissOlder than dirtCommented:
Until then maybe you could explain more about what you mean when you say "how to use it".
Martin LissOlder than dirtCommented:
I've attached a new workbook. In it do the following:

To find the macro, press Alt+F11 to open Visual Basic Editor (VBE).

In the "Project-VBAProject" pane (at the top left corner of the editor window) you'll see 'Modules" with a plus sign and a little folder icon in front of it.

Click the plus sign or double click the folder icon or 'Modules' and you'll find 'Module1'

Double-click 'Module1' and you will see the code pane for the 'FindAll' macro.

You can run the macro in two ways. Don't do it now, but the first way to do it is to place your cursor on any of the lines of code except the 'Dim' lines and then press F5.

To do it the other way, and this is the way you would normally use it, press Alt+F11 to go back to Excel and type Ctrl+Shift+F.

I assigned the Ctrl+Shift+F shortcut key to the macro by pressing Alt+F8 to open the "Macro" dialog window, selecting the macro, clicking ‘Options…’,  holding down the Shift key and then typing the letter F (I could have used any letter) and clicking ‘OK’.
Q-28678669a.xlsm
ProfessorJimJamMicrosoft Excel ExpertCommented:
Martin,

very good solution.   just one small typo in your code.   the table number in the macro should be Table1  instead of Table13.  it gives an error if not corrected to Table1
Martin LissOlder than dirtCommented:
Interesting. I'm sure it was Table13 at one time because In tested the code and it ran OK with that value, but in any case the line of code could be changed to

    Set rng = ActiveSheet.ListObjects(1).Range

and any name would work for that table.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ProfessorJimJamMicrosoft Excel ExpertCommented:
This is much better

Thanks
Martin LissOlder than dirtCommented:
@eemoon: Please read this EE article on grading which says the following and consider changing your "B" grade. If your 'Request Attention' a moderator will help you do that.

B is the grade given for acceptable solutions, or a link to an acceptable solution. A B grade means the solution given lacked some information or required you to do a good amount of extra work to resolve the problem. When closing the question, the asker should explain why a B grade was awarded.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.