Search In option now only shows formula, not Look in Values

New Contributor

I recall being able to use the find/replace option and there would be the option to look in formula and also to look in values.

Added to this, I would have used the ^# wildcard to search for a single numeric-type value (useful for pattern matches)  and then also a 'replace with found what text.

now in office 2019 pro and also office 2021 pro, the wildcard switches have gone and I cannot search in values, only formula. What's more strange is that when I attempt to use the wildcards, I get a notification/error popup telling me to use the 'Look in Values' option, but it's still not accessible.

 

 

search.jpg

4 Replies

@Hangerhead 

1) Codes such as ^# are used in Word, not in Excel, as are options such as 'Use wildcards'.

2) The option to search in Values is only available in the Find tab of the Find and Replace dialog, not in the Replace tab.

For cells that contain a value, this won't make a difference. For cells with a formula, it is impossible to replace (part of) its value with something else - it would clash with the formula.

 

Excel does not have a built-in way to specify a single digit in the 'Find what' box.

@Hans Vogelaar 

 

Using a dummy formula below I could find the cells using ^#.
="asdfds^#"

JulianoPetrukio_0-1644236982515.png

 

hi - here you are finding the explicit text ^#.

As Hans has indicated, using the wildcard searchonly works in word (where the wildcard pattern ^# = 1 single numeric character and ^#^#^# = 3 numeric characters)

I must be misremembering however, as I was so sure this wildcard search existed in excel also.
I would suggest you using the most recent array formula such as FILTER() where is possible return de disired output.