Solved

in Excel need to Find a keyword in a cell and extract it in another cell

Posted on 2011-03-22
8
430 Views
Last Modified: 2012-05-11
in Excel need to Find a keyword in a cell and extract it in another cell if found.
There are multiple keywords, so that would be set in a range.I believe this can only be achieved using VBA, I would need the code to do this.
0
Comment
Question by:yvan_vallee
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 6

Expert Comment

by:FernandoFernandes
ID: 35191513
you should try vlookup()...

take a look at Excel's help about this function.
0
 
LVL 6

Expert Comment

by:FernandoFernandes
ID: 35191520
0
 
LVL 6

Accepted Solution

by:
KnutsonBM earned 250 total points
ID: 35191622
give this a shot

=if(countif(A1,"*keyword*")>0,"keyword","")

-Brandon
0
 
LVL 7

Expert Comment

by:harr22
ID: 35191694
Brandon's should work, here is another method

=IF(SEARCH("keyword",A32),"keyword","")

-Travis
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:yvan_vallee
ID: 35191833
VLOOKUP is only fine if the cell in which I check for a keyowrd only contains that keyword. This is not my case, the keyword is part of a string. Using "TRUE" for an approximative match is not helping in this case, at least from the test I just did.
0
 
LVL 6

Expert Comment

by:KnutsonBM
ID: 35191849
if you put your keyword in with asterisks on either side it will work, "*keyword*"
0
 
LVL 6

Expert Comment

by:FernandoFernandes
ID: 35192104
please see the file attached...

i wasn't able to use a range to determine the keywords... so i used the choose function, in order to be able to choose which keyword in the range you want to work with... this is one of the solutions.

the other one is more simple, but uses multiple columns...
Book1.xlsx
0
 

Author Closing Comment

by:yvan_vallee
ID: 35192117
This solution works fine , the other proposed solution (below this one)is displaying a "#VALUE!" if it can't find the keyword. This one puts a blank which is preferable.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now