Forum home » Delegate support and help forum » Microsoft Excel Training and help » Excel
|
|
|
The UK's most regular Training information: See also: |
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel
Resolved · High Priority · Version 2007
Reply Tue 27 Oct 2009, 10:49Delegate Sarah said...
Sarah has attended:
Excel Advanced course
Excel
I have a list of data and need to find one word within a certain cell.
What formula would I use?
For upcoming training course dates see: Pricing & availability
RE: Excel
Hello Sarah
Thank you for your question and welcome to the forum.
Depending on whether your aim is just to locate the word you're looking for within the data; or if you want to be able to extract that word in some way; this might be as straightforward as using the Find feature (i.e. formula not required).
On the Home ribbon, there is a Find and Select button. If you go to Find, and then type in the word you are looking for; then click Find Next. This will go directly to the next occurence of that word within the sheet.
Kind regards
Amanda
Reply Wed 4 Nov 2009, 12:31Delegate Sarah said...
RE: Excel - COMPLICATED QUERY I WARN YOU
Hi
I'm not just looking to find the word but I need to highlight the cells which have that word so that I can then filter by the found word. So for example if I wanted to sort cat, dog, horse fields, but the data was listed as black cat, white dog, brown horse, what would the formula be to put in an entirely new column with only the words cat, dog, horse
Cheers
Sarah
RE: Excel - COMPLICATED QUERY I WARN YOU
Hi Sarah
Are the only words you would be looking for be cat, dog and horse; or are there others?
Amanda
Reply Tue 17 Nov 2009, 12:52Delegate Sarah said...
RE: Excel - COMPLICATED QUERY I WARN YOU
Yes I require a formula where in a column I will get just cat, horse, dog so that I can then sort by that data
RE: Excel - COMPLICATED QUERY I WARN YOU
Hi Sarah,
Sorry, but it's difficult to picture what you are trying to achieve. Amanda is no longer with the company, so I will be assisting you with this.
I will try my best to resolve your question (although please let me know if you've figured it out already!)
Is it possible to send a copy of the spreadsheet, and explain which column you want to put the new data (eg. 'cat' 'horse' 'dog' words)? You can e-mail to forum
and quote the URL of this forum post as a reference.
Alternatively, what about using Autofilter? Put the Custom conditions in as "Contains" and then the animals you want to filter for.
Regards, Rich
Reply Thu 19 Nov 2009, 10:13Delegate Sarah said...
RE: Excel - COMPLICATED QUERY I WARN YOU
Where do I find autofilter?
RE: Excel - COMPLICATED QUERY I WARN YOU
Hi Sarah,
Select the column you want to autofilter.
Go to Data -> Filter -> Autofilter.
You should see the first cell in the column (hopefully the column heading!) now has a drop-down arrow on the right. Click this and it will give you a list of all the entries in the column. You can select a particular entry, and it will filter the colulmn to only show rows with that value.
The other options at the top are (All) which returns to the normal view, and (Custom...) which brings up a dialogue box. In the dialogue box, change both drop-downs to be 'Contains', and select 'OR' radio button. Then type dog in the top right box, and horse in the bottom right box. Click OK and the list should only show rows where 'dog' and 'horse' are present.
Now I realise that you want to also filter for Cat, but for some reason Excel seems to only let you have 2 conditions (I find this extraordinary, but Excel isn't my forte... one of my colleagues may have another way to do this).
What I would suggest is to create another column in your spreadsheet, use autofilter as I've described above (but only filter for 'horse'). Then when the rows are filtered, enter horse in your new column. Use 'fill down' to put horse in all the other matching rows.
Now do the same for 'dog' and 'cat'. At the end, change autofilter to (All) and you should have a new column showing simple 'horse', 'cat' or 'dog'.
You can now use Autofilter on this new column, and change the autofilter to (NonBlanks) which will be the last option in the AutoFilter. This will remove all other rows from view.
Let me know how you get on.
Regards, Rich
Reply Thu 19 Nov 2009, 11:48Delegate Sarah said...
RE: Excel - COMPLICATED QUERY I WARN YOU
Hi
Yes that works
Cheers
Sarah
|