Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I would like to find all cells containing the number 18 in an Excel 2007 worksheet where the numbers are in a column with entries in General format (both numbers and strings).

When I use the Find command, enter 18 in the search box, and hit 'Find Next', Excel sequentially moves the cursor to many cells that are not 18, such as 13, 14, etc as well as 18. Or it says 'MicroSoft Excel cannot find the data you are searching for' when in fact I see cells with 18. The problem is not consistent; it did not occur when I tried the search again later. What is going on?

share|improve this question
add comment

3 Answers

up vote 0 down vote accepted

Excel does not highlight cells when you search for them. Excel shows the next cell when you click on find next. You can use "find all", then select all found cells to highlight all cells containing what your are looking for. In the "find and replace" window click on "options" and select "match entire cell content".

share|improve this answer
    
You are right, and I have replaced 'highlight' in my original question with 'moves the cursor'. The misbehavior is inconsistent - I just reran the search and now Find works fine. Next time it happens I will try 'match entire cell content' to see if that fixes it. –  KE. May 24 '12 at 20:51
    
For numbers you better check "math entire cell content" as searching for 18 will find 1187 if it is not checked. –  Ali May 25 '12 at 4:14
add comment

Make sure the Look in: has VALUES selected.

You may have sheets that seem to default the search to Look in: FORMULAS which may give what seem to be inappropriate values.

share|improve this answer
    
Thanks for this tip. –  KE. Jun 6 '12 at 12:26
add comment

Find All and Find Next gives expected results.

Refer this screenshot:-

enter image description here

Just cross verify that you are doing correct.

share|improve this answer
    
Yes, the inconsistent behavior may be a sign of sometimes doing it right, and sometimes doing it wrong. –  KE. May 29 '12 at 12:57
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.