0

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?

3 Answers 3

0

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".

2
  • 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.
    – KAE
    May 24, 2012 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, 2012 at 4:14
2

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.

0
1

Find All and Find Next gives expected results.

Refer this screenshot:-

enter image description here

Just cross verify that you are doing correct.

1
  • Yes, the inconsistent behavior may be a sign of sometimes doing it right, and sometimes doing it wrong.
    – KAE
    May 29, 2012 at 12:57

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .