Thursday, February 28, 2013

Replace with a part from Find Sting

Editplus probably is the best ascii based text editor that i have experienced so far and the most compelling reason is the find and replace capabilities that this editor allows.

I recently had this task to create an excel sheet full or formulas and just when i started writing these formulas i started wondering if i could manage a shortcut :-)

Here are the details
  1. Firstly i wrote the formula on the first row using combination of absolute and relative referencing such that it could be copied to all the rows below while still referring to the right cells.
  2. I realized i made a small mistake in the formulas and editing all the formulas again was a daunting task and so the exploration began.
  3. I used the Excel feature to display all the formulas in the rows instead of the values using "Ctrl + ~" character combination
  4. I copied all the formulas in EditPlus
  5. I landed up with a string as below (only a part is pasted the actual string was quite long)
=IF($H4>$AE$1,$V4/(($H4-$G4)/30),0)
=IF($H4>$AF$1,$W4/(($H4-$G4)/30),0)

I wanted to add a AND clause in the formula and a simple find replace would not work as a part of the replace string was variable and had to reproduced from what was being replaced (highlighted in red above). So i used the tagged expression feature of editplus to copy this into the replaced string.

A tagged expression is denoted using round brackets "( )" and can be referred in the replace string using \1. So i used the below expressions

Note: a backslash is used before the $ symbol as it is a special character and the backslash is used to tell editplus to ignore the special meaning $ symbol has to it.

Find: \$H4>(\$....)
Replace : AND($H4>\1,$G4<=\1)

and got the result

=IF(AND($H4>$AE$1,$G4<=$AE$1),$V4/(($H4-$G4)/30),0) =IF(AND($H4>$AF$1,$G4<=$AF$1),$W4/(($H4-$G4)/30),0)