Forum Discussion
Find and Replace Formulas
I have a simple formula that I only want to find replace one mathematical sign. I have many cells with the original formula and multiple s. I tried using the * in place of the cell name and it did okay finding the formula but it would not replace the formula using the *. Is there a way to do this or do I have to do the copy and paste the new formula in several columns, several worksheet
from
A1+B1-C1 to A1-B1-C1
A2+B2-C2 TO A2-B2-C2
and so on
WendyVN maybe there is another way but this might work for you if they are all in the same column:
1) convert worksheet to R1C1 style
2) find & replace
3) convert workshet back to A1 style
so step 1 is go into excel options select formulas section and click the checkbox for R1C1 style. See image below
now all the formulas will look something like =RC[-5]+RC[-4]-RC[-3]
highlight that formula and go into find and replace and paste that into the find
under replace you can change the + to - so it will be =RC[-5]-RC[-4]-RC[-3]
then go back into options and unclick the R1C1 checkbox.
For the future consider using array formulas so =A1:A100 + B1:B100 - C1:C100 which will do the calculation on all 100 rows and then if you need to change you only change the 1 cell.
Or better yet you can use LAMBDA so you define a LAMBDA function like MyCalc
MyCalc := LAMBDA(a,b,c, a+b-c)
then you have =MyCalc(A1, B1, C1) or =MyCalc(A1:A100, B1:B100, C1:C100) in the formula boxes. Then if you need to change the calculation you go back into your Name Manager and change the definition of MyCalc to be MyCalc := LAMBDA(a,b,c, a-b-c) and then all cases of that are corrected.
3 Replies
WendyVN maybe there is another way but this might work for you if they are all in the same column:
1) convert worksheet to R1C1 style
2) find & replace
3) convert workshet back to A1 style
so step 1 is go into excel options select formulas section and click the checkbox for R1C1 style. See image below
now all the formulas will look something like =RC[-5]+RC[-4]-RC[-3]
highlight that formula and go into find and replace and paste that into the find
under replace you can change the + to - so it will be =RC[-5]-RC[-4]-RC[-3]
then go back into options and unclick the R1C1 checkbox.
For the future consider using array formulas so =A1:A100 + B1:B100 - C1:C100 which will do the calculation on all 100 rows and then if you need to change you only change the 1 cell.
Or better yet you can use LAMBDA so you define a LAMBDA function like MyCalc
MyCalc := LAMBDA(a,b,c, a+b-c)
then you have =MyCalc(A1, B1, C1) or =MyCalc(A1:A100, B1:B100, C1:C100) in the formula boxes. Then if you need to change the calculation you go back into your Name Manager and change the definition of MyCalc to be MyCalc := LAMBDA(a,b,c, a-b-c) and then all cases of that are corrected.
- m_tarler Thank You. The first solution worked perfectly. I have over 23 sheets in a file. I switched to the R1C1 Style ... did the find and replace on one sheet, it worked. So I grouped the remaining sheets and it appears to have done them all (839 replacements). No they are not just one column. Multiple columns, multiple lengths. Its a sheet for tracking my inventory. Then I copy to use at trade shows so thank you again!!! I never knew what r1c1 was ... actually never noticed it before.
- glad it worked. yeah it doesn't have to be one column but needed to be the same relative distance/relationship. R1C1 format is row X column Y so R1C1 is the same as A1 and R5C13 is M5. But using [ ] says to excel to use relative position so R[3]C[-2] means the cell 3 rows down and 2 columns to the left and RC[-4] means same row but 4 columns to the left.
the other 2 points at the end were just some tip to think about going forward about how to potentially improve the maintanability of your sheets.
