"Find and Replace" missing the ability to replace values?

**dylanemcgregor** · 02-05-2009, 04:25 PM

This problem has come up fairly frequently lately, and I'm not sure how to fix it, or if this is by design...but in Excel 2003 I can't seem to do a "Find and Replace" based on the value of a cell. I can do a find, based on cell value, but the moment I change to the replace tab, the "values" and "comments" are missing from the "look in" dropdown.

I've only noticed this when I'm trying to replace on a filtered list, so I'm not sure if that is part of the issue.

If there is no fix to the above problem, perhaps someone can suggest an alternative way of arriving at the same goal. Basically I have a worksheet with a number of filtered columns. They are filtered just right, using custom filtering, and so I do not want to undo the filters. In some columns I have formulas that are returning #VALUE! errors. I'd like to replace all of these cells with NA. Any suggestions?

**shg** · 02-05-2009, 05:04 PM

Edit > Go to > Special, tick Formulas, tick Errors, and then group-enter whatever you want.

**dylanemcgregor** · 02-05-2009, 11:03 PM

Originally Posted by shg

Edit > Go to > Special, tick Formulas, tick Errors, and then group-enter whatever you want.

Feeling dense here, but how do you "group enter" something once all the cells with errors are highlighted? I've tried just entering "NA" into the function toolbar, but got nothing.

**shg** · 02-06-2009, 01:16 AM

Type something into the formula bar, press and hold the Ctrl key, then press Enter.

**Zhuangzi** · 11-26-2012, 07:31 PM

Originally Posted by dylanemcgregor

This problem has come up fairly frequently lately, and I'm not sure how to fix it, or if this is by design...but in Excel 2003 I can't seem to do a "Find and Replace" based on the value of a cell. I can do a find, based on cell value, but the moment I change to the replace tab, the "values" and "comments" are missing from the "look in" dropdown.

I've only noticed this when I'm trying to replace on a filtered list, so I'm not sure if that is part of the issue.

If there is no fix to the above problem, perhaps someone can suggest an alternative way of arriving at the same goal. Basically I have a worksheet with a number of filtered columns. They are filtered just right, using custom filtering, and so I do not want to undo the filters. In some columns I have formulas that are returning #VALUE! errors. I'd like to replace all of these cells with NA. Any suggestions?

I just encountered this problem. I copied all data into a fresh worksheet, and the missing Find options came back. In my case, the problem seems related to Values no longer being calculated in the problematic worksheet, which is what led me to notice the overlap in our issues. (for me, i may have changed some option for the worksheet causing values to no longer be calculated from formulas, but if I did & this isn't some Microsoft error, it was accomplished on an entirely accidental basis)

Hope this helps someone somewhere.

**avemaria0831** · 06-05-2013, 12:42 AM

I encountered a problem very similar. When I would try to "find and replace" a VALUE vs. a formula, I could not choose the "value" option. I found a link that said it could be because the sheet is protected, but my sheet is NOT protected. I am searching for the word ARCADIA with two trailing spaces. I want to replace it with ARCADIA and ONE trailing space. When I look for ARCADIA?? (using ? as a wildcard), and replace with ARCADIA and a space, it worked. But I am positive I have not had to use a wildcard in the past. I've done this type of search and replace tons of times. So still don't know why this happened, but at least its fixed.

**arlu1201** · 06-05-2013, 01:13 AM

Avemaria,

Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.