Find and Replace bug - Can not look in values

[AirMaximus88]

Hi everyone!

First time poster here, I do apologise if I contravene any forum rules.

I am having trouble with missing aspects of the Find and Replace function. The Find function allows me to look in Formulas, Values and Comments, but the Find and Replace function only allows me to look in Formulas. I could have sworn that I previously used this function to look in the Values of cells rather than just the Formulas. Any ideas what I've done?

Pictures attached:

Fig 1. Find function allows me to look in Formulas, Values and Comments.

Fig 2. Find and Replace function only allows me to look in Formulas.


Thanks for your help,

Max



P.S. As an afterthought I should probably have mentioned my current version - Excel 2010 Ver. 14.0.7113.5005 (32-bit)

 

[JLGWhiz]

Hi everyone!

First time poster here, I do apologise if I contravene any forum rules.

I am having trouble with missing aspects of the Find and Replace function. The Find function allows me to look in Formulas, Values and Comments, but the Find and Replace function only allows me to look in Formulas. I could have sworn that I previously used this function to look in the Values of cells rather than just the Formulas. Any ideas what I've done?

Pictures attached:

Fig 1. Find function allows me to look in Formulas, Values and Comments.

Fig 2. Find and Replace function only allows me to look in Formulas.


Thanks for your help,

Max



P.S. As an afterthought I should probably have mentioned my current version - Excel 2010 Ver. 14.0.7113.5005 (32-bit)

I get the same display in 2007 version. But it works with values. I expect that the find part is why they only used the formulas for that box. If you are looking for wild card (*) for example, it would need to look in formulas. But it is not formulas only, it is values and formulas. Maybe they should have expressed it that way in the dialog box.

 

[AirMaximus88]

I'm not sure if I fully understand your reply, but my version certainly doesn't work with values. I still really need this functionality if anyone can help me.

 

[Peter_SSs]

Welcome to the MrExcel board!

What you are describing has been the case for many versions of Excel, and possibly always.
Also, as JLGWhiz has stated, the Replace does work with values, so something else must be wrong in your case.

Can you give us a few examples of cell values and explain what you are trying to replace and what the replacement should be. Also explain in what way it is currently not working. Doing nothing? Replacing wrong values? Deleting all values? Crashing Excel, etc

To demonstrate. In the sheet below I started with columns A & B identical, with manually entered values. In column B I did a Find/Replace (Formulas) to find "a1" and replace with "Z" and you can see that cells B1, and B10:B12 have changed.

Excel Workbook
	A	B
1	a1	Z
2	a2	a2
3	a3	a3
4	a4	a4
5	a5	a5
6	a6	a6
7	a7	a7
8	a8	a8
9	a9	a9
10	a10	Z0
11	a11	Z1
12	a12	Z2
13
Replace

 

[AirMaximus88]

I need to find and replace data produced from a formula; I get given data sheets aimed at reconciling data sets between an old and new system. The strings produced by the formula are:

"Not Present" - if the data is found on the old system but not the new system

"Present - don't agree" - if the unique identifier is found for a data point in both data sets, but there are differences in some of the information, i.e. Last service date

"Present" - if the data is an exact match on both systems

"New Data" - data that is only present on the new system.



I need to find and replace cells with New Data or Present - don't agree and change them, but without copy and pasting the whole sheet as values because the other data points need to keep pulling from their sources...

 

[Peter_SSs]

I need to find and replace data produced from a formula;..

OK, that is the issue. Find/Replace can replace values, but what you are wanting to do is effectively replace the whole formula.

I need to find and replace cells with New Data or Present - don't agree and change them, ...

Then why not just change your existing formulas to return whatever replacement values you had in mind?

Find/Replace (looking in Formulas) could probably do this for you. If you need further help, it would be good to see at least one of the formulas producing the results you listed in your previous post.

 

[MaryCJ]

OK, that is the issue. Find/Replace can replace values, but what you are wanting to do is effectively replace the whole formula.



Then why not just change your existing formulas to return whatever replacement values you had in mind?

Find/Replace (looking in Formulas) could probably do this for you. If you need further help, it would be good to see at least one of the formulas producing the results you listed in your previous post.

I recently changed PCs and now my Excel is defaulting to "look in" Formulas, not Values. I prefer Values as the default. Anyone have a suggestion with adjusting to my preference in Excel?

 

[Peter_SSs]

I recently changed PCs and now my Excel is defaulting to "look in" Formulas, not Values. I prefer Values as the default. Anyone have a suggestion with adjusting to my preference in Excel?

Welcome to the MrExcel board!

As far as I am aware the default (each time to start Excel) has always been Formulas. A work-around of sorts it given here.

 

[Flynn36]

Hi, me too, have the same problem. After read the discussion, I've got an idea for alternative solution.
What I did, I copy the whole column, then right click to choose 'paste values' into new column.
Then delete the old column (remember do backup just in case). Now I can proceed with 'find and replace task'.
Thank you guys!

 

[frankzolf]

Go to google sheets and do it there (same ctrl-H command)... there are some obvious things to consider such as security, but's easier than formulae or whatever else got suggested (macros'? c'mon)