Finding Numbers

Finding numbers with a comma format

By Neale Blackwood
Excel has a bug in its Find routine that you may have experienced. If you have formatted formula using the comma (,) number format which displays 1000 as 1,000 then you might struggle to find numbers of 1000 and above.

Some background to how Find works. The range you have selected prior to running Find is important. If you select a single cell then Excel will search the whole sheet. If you have a range selected then only that range will be searched.

Try this. In a blank sheet enter 1000 in both A1 and A2. In cell B1 enter =A1 and copy it to B2. Format B1 and B2 using the comma format.

Select cell C1 and press Ctrl + f to open the Find dialog. Click the Options button and select Values in the Look In drop down. Enter 1000 in the Find What box and click Find All. Notice that B1 and B2 are not found. Now amend the Find What to 1,000 and click Find All and see the results. This time it should find B1 and B2 but not A1 and A2.

So be warned finding comma formatted formula values in Excel has its problems. This is still an issue in Excel 2007.

Note: Changing the Look In option to Values is important in normal searches. The default for the Look In option is Formula, which means the formula itself is searched rather than its result. That is another common reason for not finding things in Excel.