Excel 2007: How to replace part of formula with another formula?

Question

I need to replace the $D49 with

SERROR(VLOOKUP($B49 & " Total",Flt_S2D1,$Z$47,0)),0,VLOOKUP($B49 & " Total",Flt_S2D1,$Z$47,0))** in below formula.

=IFERROR(IF($Z$38="CBO Dmd",$C49,IF($Z$38="CBO Dmd-Cust Raw",$D49*($Y49/$E49),IF($Z$38="CBO Dmd-Pr CBO",$D49*($X49/$E49),IF($Z$38="CBO Dmd-BAB Grs CGID",$D49*($W49/$E49),IF($Z$38="CBO Dmd-Bill Grs CGID",$D49*($V49/$E49),$D49*($CC49/$E49)))))),0)

I have googled and find some articles to do this, but those are related to replacing a part of formula with calculated result or cell value.

I can replace $D49 with required value by doing multiple times, but is there any way to replace at a time like find and replace all, which we are doing for text replace.

Answer 1

Normal Search and Replace works (shortcut Ctrl+H). Just make sure you choose 'Look in:' Formulas

Answer 2

This solution doesnt require any additional VBA code and will work with Google Spreadsheet and almost all versions of Excel.

Its relatively easy to do complex fine and replace in multiple excel cells using a combination of VIM, Excel and notepad. Because VIM can do very complex search and replace operations, you can pretty much do almost search and replace with this method.

If you have vim installed on windows, you don’t need notepad. Assumes Excel is installed on Windows

Steps

1. Press `Ctrl-``, to change the displayed values to formulas (https://www.thoughtco.com/show-hide-formulas-in-excel-and-google-spreadsheets-3123884)
2. Copy the formulas from excel
3. Copy the formulae into VIM: press i while in normal mode in vim. Press Shift-Insert and Escape
4. Use ex mode search and replace with Regular expressions to do search and replace by pressing :%s/old/new/gc. Here old is the part of the expression you want to substitute and new is the new formula you want to substitute with. https://stackoverflow.com/a/505861/4752883
5. Make sure to do a %s/[[:space:]]*//gc to remove any blank spaces in front of each line in ex mode in vim
6. Save the file as a .txt file, if you are using vim in linux.
7. Copy the formula from the .txt file by pressing Ctrl-A and Ctrl-V and enter it into the first cell

Answer 3

You can still do search and replace on cells with a formula. It will be applied to the forumla, not the result you see. Try it out and see if it works.