As of Excel 2010, it seems a basic function has been removed. In the Find/Replace dialog box, the "look in" option has historically had "formulas, values, comments" in it. Now those options only exist in the Find dialog box and the Replace dialog box only has "formulas" (the drop down still has 3 placeholders but 2 are empty).
Note: Formulas, Values and Comments are only available on the Find tab; whereas only Formulas are available on the Replace tab.
Is it possible to get the old functionality back? If not, what is the best workaround?
1 Answer 1
From Microsoft Answers
... using the Find tab, you can find all values as specified. Then select all results using either Ctrl + A or using Ctrl and mouse to highlight the ones you want. Then close the find dialog window. All of the cells with the specified value should be selected. Type in the new value/formula and press Ctrl + Enter. Voila! All cells with the specified value have been replaced with the new value.
-
1This turns a one click process into at least 5 steps. Is there any better way? Perhaps something that can be done with Macros or some other developer option?f.thorpe– f.thorpe2015年12月03日 23:46:41 +00:00Commented Dec 3, 2015 at 23:46
-
it's actually 2 additional steps / clicks then the old way :)Scott Holtzman– Scott Holtzman2015年12月04日 02:34:50 +00:00Commented Dec 4, 2015 at 2:34
-
rather than mousing to "values" and clicking replace all, I have to press ctrl + A then close then ctrl + enter.. so 2 mouse actions into 2 keyboards + one mouse + 2 keyboards... yuckf.thorpe– f.thorpe2015年12月04日 03:29:27 +00:00Commented Dec 4, 2015 at 3:29
-
1since you asked, there is a vba option, if you know how to use VBA:
Range("A1:A100").Replace("old","new")but I don't think you want a custom function just for this ...Scott Holtzman– Scott Holtzman2015年12月04日 03:45:35 +00:00Commented Dec 4, 2015 at 3:45
You must log in to answer this question.
Explore related questions
See similar questions with these tags.