excel

Public Schedule Face-to-Face & Virtual Instructor-Led Training - View dates & book

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Excel

Excel

resolvedResolved · High Priority · Version 2007

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?

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

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

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@stl-training.co.uk 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

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

RE: Excel - COMPLICATED QUERY I WARN YOU

Hi

Yes that works

Cheers

Sarah

 

Training courses

 

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
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.


 

Excel tip:

Create Equations in Excel 2010

Here's how to create basic mathematical
equations in your Excel 2010 worksheet.

1) On the Ribbon, click the Insert tab
2) In the Symbols group, click the arrow next to Equation
3) Select from the equations and the equation will be inserted in a text box

Or you can create your own equation:

1) Insert and select the text box
2) On the Ribbon, click the Insert tab
3) In the Symbols group, click Equation
4) The Equation Tools Design Ribbon will now be displayed

View all Excel hints and tips


Server loaded in 0.08 secs.