Ask Your Question

Find and replace within formula

asked 2018-09-26 19:32:28 +0200

mike65535 gravatar image

updated 2018-09-26 20:15:34 +0200

I want to perform a find and replace within a formula.


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.


edit retag flag offensive close merge delete


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.

Lupp gravatar imageLupp ( 2018-09-26 19:54:44 +0200 )edit

Ok, thanks for that.

mike65535 gravatar imagemike65535 ( 2018-09-26 20:14:32 +0200 )edit

1 Answer

Sort by » oldest newest most voted

answered 2018-09-26 19:48:32 +0200

Lupp gravatar image

updated 2018-09-27 00:29:47 +0200

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.

edit flag offensive delete link more


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.

mike65535 gravatar imagemike65535 ( 2018-09-26 19:58:59 +0200 )edit

If I select at least two cells, I can get this to work. Selecting one cell always tells me "Search key not found".

mike65535 gravatar imagemike65535 ( 2018-09-26 20:14:02 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2018-09-26 19:32:28 +0200

Seen: 315 times

Last updated: Sep 27 '18