Excel 2010, find & replace

[Franky]

Hi guys,

In my Excel 2010 workbook there is a cell with the value of "90" in type of number/general. When I go to find and replace dialog box and try to find the number "90" by typing it in Find what rectangle, then I want the program looks for it in formulas (look in: Formulas). But it shows that "90" as the result! Why? I said it to look for it in Formulas not in values.

 

[ignatzatsonic]

If I understand you correctly, the 90 is a value computed from a formula in that cell.

You should search for 90 as a value if you want to find that cell.

Searching for 90 in a formula would mean that you are searching for the number "90" somewhere within a formula, not within a cell value.

If the formula in that cell was =110-90, I would expect that a search for 90 in formulas would find that cell.

If the formula in that cell was =110-20, I would not expect that a search for 90 in formulas would find that cell. A search for 90 in values would find it.

If the formula in that cell was =110-90+70, I'd expect that cell to be found by both searching for value or searching in formulas because 90 is the value of that cell and 90 is also found in the formula.

 

[Franky]

Thank you for your comprehensive explanation But one topic. Suppose I enter (by hand) the number 90 into one cell, clear till now?, then I search for number 90 with look in Formulas. But again the program finds that 90, while it's not the result of a formula and itself also isn't a formula so logically since it has nothing to do with the formula so Look in formula shouldn't find it, not?

 

[ignatzatsonic]

I just did a test with these values and formulas:

90
=90-3
=93-3

The first and last have a value of 90; the first has only 90 in the cell, and the second has 90 only in the formula.

Results:

Look in values finds the first and last. As expected.

Look in formulas finds the first and second. Not as expected.

That confirms your test.

However "look in formulas" with "match entire cell contents" finds only the first.

So, it's either a bug, or "look in formulas" actually means "look in formulas as well as values".

To get the result you want, you'd have to check "match entire cell contents".

It may well be intentional by Microsoft. I have no idea. I'd never run into that before and don't do much Excel searching.

 

[Franky]

Thanks ignatzatsonic. You did what I would need.
Although my English is broken but as far as I know, The phrase "Look in Formula" doesn't mean "look in formulas as well as values". It's a bug just like other numerous bugs existing in MS products.

 

[ignatzatsonic]

Thanks ignatzatsonic. You did what I would need.
Although my English is broken but as far as I know, The phrase "Look in Formula" doesn't mean "look in formulas as well as values". It's a bug just like other numerous bugs existing in MS products.

I tend to agree with you.

It's at least careless and confusing language. Your English is not even slightly broken.

They should use wording like "look in formulas only" or "look in values only" or "look in formulas and values".

 

[RickL1]

This caught my eye since I have used Open Office spreadsheets in the past where I did lots of find & replace in formulas. I repeated ignatzatsonic's test in Excel 2000 and got exactly the same results. I also consider this to be an unfortunate bug, but when a bug stays around that long it becomes a feature that can never be tampered with.

Rick

 

[TanyaC]

I just did a test with these values and formulas:

90
=90-3
=93-3

Results:

Look in formulas finds the first and second. Not as expected.

Sorry to jump in here guys. And please forgive me if I seem overly pompous.

The result is expected, because the search looks in formulas for the value you specify. It is not looking at the result of the formula being equal to the search criteria. It's not actually a bug.

So not seeing the last one is correct.

I think you are making the same mistake I made, thinking that it should be looking at the result of the formula, not the formula itself.

That's how it was explained to me on the excel course I did in 2007.