Find Replace Around Reference

[Bullock]

I inherited a workbook that uses =Round functions unnecessarily. I want to use Find/Replace or VBA to replace anywhere I have =ROUND(A2,1)-ROUND(C2,1) to just =A2-C2 while preserving the cell references, as they are each different. Thanks

 

[Villalobos]

Hi,

Try this: press CTRL+H -> at the left bottom corner there's a Look in: Formulas.

 

[Bullock]

Hi,

Try this: press CTRL+H -> at the left bottom corner there's a Look in: Formulas.

Thanks, I have tried the Find/Replace dialog, but the problem is I need to replace hundreds of instances, each with a unique cell reference that I want to keep. Essentially I want to delete each of the round functions and keep only the two cell references and the minus sign.

 

[NARAYANK991]

Hi ,

The problem is that you cannot do the Find and Replace in stages , since it is a formula. Try the following procedure after making a copy of your workbook.

1. Find :

=ROUND(

Replace :

|=ROUND(

This will now convert all the formulae to text.

2. Find :

ROUND(

Replace :


This will eliminate all instances of the above text.

3. Find :

,1)

Replace :


This will eliminate all instances of the above text.

4. Find :

|

Replace :


This will now convert the text back to the desired formula.

Narayan

 

[Bullock]

Hi ,

The problem is that you cannot do the Find and Replace in stages , since it is a formula. Try the following procedure after making a copy of your workbook.

...

Narayan

Thanks, exactly what I needed!