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)

26 comments:

Anonymous said...

Hi would you mind lettіng me know ωhiсh hosting
company уоu're working with? I've loaded уοur blog in 3 ԁifferent browseгs and ӏ muѕt say this blog
loads a lot faster thеn most. Can you suggest a gοoԁ ωeb hostіng provider аt a honest ρrice?
Thank yοu, I apprеciate it!

Viѕit my wеbsite :: dallas seo

Anonymous said...

I love your blog.. very nice colors & theme. Did you create this website yourself or did you hire someone to do
it for you? Plz respond as I'm looking to design my own blog and would like to know where u got this from. kudos

my webpage :: How much should You weigh

Anonymous said...

Some truly wonderful articles on this site, regards for contribution.
"Once, power was considered a masculine attribute. In fact, power has no sex." by Katharine
Graham.

Take a look at my blog post - Great GPT Site

Anonymous said...

Hi there! This is my first visit to your blog! We are a group of volunteers
and starting a new initiative in a community in the same niche.
Your blog provided us beneficial information to work on.
You have done a extraordinary job!

Feel free to surf to my page :: just click the next web page

Anonymous said...

Hi there colleagues, pleasant piece of writing and pleasant
urging commented here, I am in fact enjoying by
these.

Also visit my blog victorinox swiss army watches for women

Anonymous said...

Hi are using Wordpress for your blog platform?
I'm new to the blog world but I'm trying to get started and create my own.
Do you need any coding knowledge to make your own blog?
Any help would be really appreciated!

my web-site; women's victorinox swiss army

Anonymous said...

When someone writes an post he/she maintains the thought of a user in his/her mind that how
a user can be aware of it. Thus that's why this piece of writing is great. Thanks!

my weblog ... www.glowtrend.com

Anonymous said...

Hey there! I just wanted to ask if you ever have any trouble with hackers?
My last blog (wordpress) was hacked and I ended up losing months of hard work due to no backup.
Do you have any solutions to prevent hackers?


Also visit my page remote control cars

Anonymous said...

Thanks for any other informative site. The
place else may I am getting that type of information written in such an ideal
method? I've a project that I am just now operating on, and I have been on the glance out for such information.

Check out my homepage; get dragonvale

Anonymous said...

I'm gone to convey my little brother, that he should also go to see this website on regular basis to take updated from hottest news.

Also visit my site: remote control vehicles

Anonymous said...

Nice post. I learn something new and challenging on
websites I stumbleupon on a daily basis. It's always helpful to read articles from other writers and practice a little something from other sites.

Also visit my web site ... Full TV Series Download

Anonymous said...

Have you ever considered writing an ebook or guest authoring
on other websites? I have a blog centered on the same subjects you
discuss and would really like to have you share some stories/information.
I know my visitors would appreciate your work. If you're even remotely interested, feel free to shoot me an email.

Here is my homepage - womens vestal watches

Anonymous said...

Tremendous things here. I am very satisfied to see your
article. Thank you so much and I'm having a look forward to touch you. Will you please drop me a e-mail?

My blog post Beta Force

Anonymous said...

Hey there! Do you know if they make any plugins to help with Search Engine Optimization?
I'm trying to get my blog to rank for some targeted keywords but I'm not seeing very good results.
If you know of any please share. Thank you!

Here is my blog; armitron

Anonymous said...

Hi there friends, how is everything, and what you would like to say concerning this
post, in my view its truly awesome in favor of me.


Also visit my web site - Saphire Ecig Review

Anonymous said...

Marvelous, what a webpage it is! This weblog gives valuable information to us, keep it up.


Here is my web page: Muscle REv Xtreme Supplement

Anonymous said...

What's up all, here every one is sharing these kinds of know-how, therefore it's fastidious to read this webpage, and I used to visit this web site everyday.



my web blog; Le derme luxe review

Anonymous said...

My brother recommended I might like this blog.
He used to be entirely right. This publish actually made my day.
You cann't consider simply how much time I had spent for this info! Thanks!

Feel free to surf to my page: anti aging creams

Anonymous said...

Hey I am so delighted I found your blog page, I really found you by error, while
I was researching on Yahoo for something else, Nonetheless
I am here now and would just like to say many thanks for a remarkable post and a all round exciting blog (I also love the theme/design), I don’t have time to read through
it all at the moment but I have bookmarked it and also included your RSS
feeds, so when I have time I will be back to read a lot more, Please do
keep up the great work.

my web blog Test Force Xtreme

Anonymous said...

Informative article, just what I wanted to find.

my webpage; Test Force Xtreme

Anonymous said...

I bought an Arrow shed in September and it just collaped under a really modest quantity of snow.

Do you sell replacement parts, like an entire
roof.

my web page ... arrow newport shed Instructions

Anonymous said...

Thanks on your marvelous posting! I genuinely enjoyed reading it, you
can be a great author. I will ensure that I
bookmark your blog and may come back sometime soon. I want
to encourage you to continue your great
posts, have a nice holiday weekend!

my weblog - Beta Force And Max Thermo Burn

Anonymous said...

I do not even know how I ended up here, but I thought this post was good.
I don't know who you are but definitely you're going to a
famous blogger if you aren't already ;) Cheers!

Also visit my homepage; minecraft premium account generator

Anonymous said...

I was curious if you ever thought of changing the layout of your website?
Its very well written; I love what youve got to say.
But maybe you could a little more in the way of content so people could connect with
it better. Youve got an awful lot of text for only having 1 or 2 pictures.
Maybe you could space it out better?

Look at my web site ... castle ville quests

Anonymous said...

Hey there just wanted to give you a quick heads up and let you know a few
of the images aren't loading properly. I'm not sure why but
I think its a linking issue. I've tried it in two different browsers and both show the same results.

Feel free to surf to my blog post ... Test Force Xtreme

Anonymous said...

Remarkable issues here. I'm very satisfied to look your post. Thanks so much and I'm having a
look forward to contact you. Will you please drop me
a mail?

Feel free to surf to my blog post - Work From Home