Windows 7 Forums

Welcome to Windows 7 Forums. Our forum is dedicated to helping you find support and solutions for any problems regarding your Windows 7 PC be it Dell, HP, Acer, Asus or a custom build. We also provide an extensive Windows 7 tutorial section that covers a wide range of tips and tricks.


Windows 7: Excel 2010, find & replace

08 May 2014   #1
Franky

windows 7 32-bit
 
 
Excel 2010, find & replace

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.


My System SpecsSystem Spec
.
08 May 2014   #2
ignatzatsonic

Microsoft Community Contributor Award Recipient

Windows 7 Home Premium SP1, 64-bit
 
 

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.
My System SpecsSystem Spec
08 May 2014   #3
Franky

windows 7 32-bit
 
 

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?
My System SpecsSystem Spec
.

08 May 2014   #4
ignatzatsonic

Microsoft Community Contributor Award Recipient

Windows 7 Home Premium SP1, 64-bit
 
 

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.
My System SpecsSystem Spec
08 May 2014   #5
Franky

windows 7 32-bit
 
 

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.
My System SpecsSystem Spec
08 May 2014   #6
ignatzatsonic

Microsoft Community Contributor Award Recipient

Windows 7 Home Premium SP1, 64-bit
 
 

Quote   Quote: Originally Posted by Kurdman View Post
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".
My System SpecsSystem Spec
21 May 2014   #7
RickL1

Windows 7 Home 32bit
 
 

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
My System SpecsSystem Spec
28 May 2014   #8
TanyaC

Linux Mint 17 Cinnamon | Win 7 Ult x64
 
 

Quote   Quote: Originally Posted by ignatzatsonic View Post
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.
My System SpecsSystem Spec
Reply

 Excel 2010, find & replace




Thread Tools Search this Thread
Search this Thread:

Advanced Search




Similar help and support threads
Thread Forum
How to go back from Excel 2010 64 bit to Excel 2010 32 bit
I have an important program that uses a DLL that only works with Excel 2010 32 bit. I'm running Excel 2010 64 bit now -- how do I install the 32 bit version? I have the CD which has both versions on it. But when I run Setup, it gives me options that I don't understand for this limited purpose. In...
Microsoft Office
Microsoft Office 2007 Excel Find and Replace Command
I know I can do this as I have done it in the past a long time ago. Using the Find and Replace option add and extension to my sku numbers, such as: SKU # 8686708052 I have a column of these sku's and want to add the extension .jpg to all of them at one time! 8686708052.jpg
Microsoft Office
best soft to find pass for Excel 2010 files ??
best soft to find pass for Excel 2010 files ?? my Uncle create very important programs on Excel 2010 which tooks over 1-2 months to create but now they forgot its password I want to break its pass to help them
Microsoft Office
Word Document find and replace, find duplicates
I have a document that is hundreds of pages long. Within this document there are certain sentences, phrases that repeat and I need to find them in a practical manner. Now I could go through manually, look for lines I suspect are repeated elsewhere and search for them, but that would take...
Microsoft Office
Find and Replace
Excel 2007 - I would like to do a Find and Replace a word and when I do it changes the entire cell by taking the words which have color and truning them black and changing my fonts to one size. How can I just Find and Replace just one word in a cell? Is there anyway I can do a find and have all the...
Microsoft Office
Formatting Excel 2010, Excel 2003
Hello, I want to create a table as in the drawing below, where the first cell is table-wide (as it contains the heading common (pertaining?) to all columns and rows under it) but, and this is where I cannot get it together, where those cells are divided into several columns. It's a cinch when...
Microsoft Office


Our Sites

Site Links

About Us

Find Us

Windows 7 Forums is an independent web site and has not been authorized, sponsored, or otherwise approved by Microsoft Corporation. "Windows 7" and related materials are trademarks of Microsoft Corp.

Designer Media Ltd

All times are GMT -5. The time now is 13:29.

Twitter Facebook Google+



Windows 7 Forums

Seven Forums Android App Seven Forums IOS App