**Please Note: **
This article is written for users of the following Microsoft Excel versions: 2007, 2010, and 2013. If you are using an earlier version (Excel 2003 or earlier), *this tip may not work for you*. For a version of this tip written specifically for earlier versions of Excel, click here: Finding All Instances of a Value.

Mike uses **Ctrl+F** to find information within cells. This works great with text, but it doesn't do what he needs it to do when searching for numbers. For instance, if he searches for "1500" then it will find any cell that contains the value, regardless of formatting (such as 1500, 1,500, 1,500.00, or $1,500), provided that Mike sets the "Look In" setting in the Find dialog box to "Formulas." But, it won't find 1500 when that value is the result of a formula, such as =750*2.

If Mike sets the "Look In" setting to "Values," then Find only finds the value 1500 along with any formula that returns 1500, provided he doesn't have the cell formatted in some other manner (such as a format that shows the result as $1,500). Mike wonders how he can use Find to find any variation of 1500, regardless of formatting or whether the value is derived by formula or not.

The short answer is that this cannot be done. When you use Excel's Find tool, the Find dialog box includes ways to instruct Excel where it should look for what you want; this is the "Look In" drop-down list. There are three options in the drop-down list:

- Formulas
- Values
- Comments

Looking in Comments should be self-explanatory—Excel limits its searching to any comments in the worksheet. The other two options ignore comments, but find information differently from each other. As Mike notes, a setting of Formulas will find any cell that contains 1500, regardless of the way that the cell is formatted. It won't, however, find a *result* of 1500 if the value 1500 is not in the formula itself. For example, consider the following two formulas:

=750*2 =1+1500-1

The result of both of these is 1500. However, searching for 1500 with "Look In" set to Formulas will not find the cell with the first formula, but it will find the cell with the second formula. Why? Because the first formula doesn't contain 1500, while the second one does.

If you change the "Look In" drop-down setting to Values, then Excel finds formula results that are 1500, provided there is no formatting that makes the value look differently (such as adding commas or dollar signs).

Regardless of the setting of the "Look In" drop down, something is missing—Excel ignores some results or some values that you don't want ignored. The only way that we've been able to get around this problem and expand what Excel will find is to do a search for "1*500" (without the quote marks) with the "Look In" setting as Values. Excel will then find the 1500 even if there is a dollar sign and a comma in what is shown.

The drawback to this approach is that using the search wildcards in this manner (the asterisk finds any number of characters) results in matches for values such as 321500 or 32178500. This is, of course, because those numbers include the number 1 followed by any number of characters and then the numbers 500. For some instances this can be a bigger drawback than the original problem.

Other than this workaround, we know of no other solution short of creating a macro to do your searching.

*ExcelTips* is your source for cost-effective Microsoft Excel training.
This tip (8314) applies to Microsoft Excel 2007, 2010, and 2013. You can find a version of this tip for the older menu interface of Excel here: **Finding All Instances of a Value**.

**Comprehensive VBA Guide** Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out *Mastering VBA for Office 2010* today!

If you need to find where line breaks are located in cells, there are a couple of ways you can proceed. Here's a quick ...

Discover MoreThe Find and Replace capabilities of Excel allow you to easily locate all the cells in a worksheet that contain specific ...

Discover MoreTired of the Find and Replace dialog box blocking the view of your worksheet when you are searching for information? Do ...

Discover More**FREE SERVICE:** Get tips like this every week in *ExcelTips,* a free productivity newsletter. Enter your address and click "Subscribe."

2021-11-05 05:08:01

Leslie Glasser

2021-07-04 02:18:33

Roy

If I use "?" what I find is that Find returns any cell that contains a single character between two doublequotes. So "y" is returned even with the literal "?" specified, not "y", and so would be "="&A3 (criterion form for some formulas) and so on. It reads the text of a formula, not necessarily its result which is interesting. But it is literal about the character being a single character string. For example, it finds NEITHER of $1,500 and $10,500. Of course, that looking in Formulas (Options settings, so I guess not surprising it read values from the formulas. It WILL return BOTH the $1,500 and $10,500, not just one of them, if searching in Values.)

Using "??" seems to mean find any doublequoted strings cells or formulas that contain two or more characters, but not those with a single doublequoted character. Tres interesting.

Searching for a literal value, like "5" I get nothing unless the entry the cell is 5. So I don't quite get Ben B's point.

* and "*" return different results, which is interesting.

MS describes the SPILL array results as all being in some given cell and displayed in however many cells. That seems to be true as Find using * returns just the appropriate cell, not the whole display range. Something useful seems just around some corner in that. Maybe VBA can crack into that repository.

Mmm... perhaps he's just doublequoting for formality and really means just the bare character ?... THAT does produce something but it's the same result as for a bare asterisk. Still not understanding the comment I guess.

But the first part seems pretty interesting.

2016-08-30 11:03:08

Ben B

2015-05-09 08:06:45

Graham

Got a version of Excel that uses the
ribbon interface (Excel 2007 or later)?
**This site is for you!** If you
use an earlier version of Excel, visit
our *ExcelTips* site focusing on the menu interface.

**FREE SERVICE:** Get tips like this every week in *ExcelTips,* a free productivity newsletter. Enter your address and click "Subscribe."

Copyright © 2022 Sharon Parq Associates, Inc.

## Comments