I want to perform a find and replace within a formula.
=VALUE(MID($C$2;1;1)<>MID($A3;1;1))+VALUE(MID($C$2;2;1)<>MID($A3;2;1))+VALUE(MID($C$2;3;1)<>MID($A3;3;1))...
I want to change the multiple instances of $C$2 to some other value - say $D$4- but only in one cell. There will be other instances of $C$2 in other cells that I don’t want to change. To be clear: I’d like to change every $C$2 to $D$4 in one cell, but not in any other cell.
Thanks.
Please consider to study this topic. In specific you should use the semicolon as the parameter delimiter to meet the needs of formula exchange in international forums.
Ok, thanks for that.
To do it in situ you ned to use F&R whether you want to apply the replacement to many cells or just to one. The option to choose in both cases is Current selection only.
To get the result in a different cell as a formula result or as an intermediary result of a subexpression in a formula use the SUBSTITUTE() function, or in less common cases the REPLACE() function. (What looks like a formula in your post doesn’t tell me much about what you actually want or need.)
(Edit 2018-09-27 00:25 CEST regarding the recent comments by the OP:)
A cell you just went to (single click, cursor keys) is not selected in the full sense. It only has the input focus what is shown by the applied cell-frame. If you Ctrl+click the same cell, it will be highlighted. Then it is also selected in the sense the ‘Current selection only’ of F&R is based on.
In addition: Your search expression and also the replacement contain special characters. Make sure that RegEx NOT is enabled. Search in Formulae, on the other hand must be enabled.
I’ve tried Current Selection only but that doesn’t work for me.
steps:
My example formula is in a cell. I select that cell, type CTRL-H, click “Other options” and select “Current selection only”, enter $C$2 into the “find field” (and anything else into the “replace” field) and hit “Find next” and it tells me “Search key not found” so I can’t even get started replacing anything.
If I select at least two cells, I can get this to work. Selecting one cell always tells me “Search key not found”.
I tested this and it works in v 7.2.6.2 on w10
thx! i was not aware of the difference of input focus and selection as well!