Need help with search and replace pattern

Question

0.00/5 (No votes)

See more:

Hi guys,

As very soon will be very clear I'm very much a beginner. If I hadn't been, I'd probably find a neat expression to fill in the "very" for me :p Anyway - I hope someone here could help me out with an expression I've been looking at to save some time editing an excel worksheet.

The sample text I have is the following:

Load
#=MROUND(('Testing Program'!L28)*(AW6/100);2,5);IF(ISBLANK('Testing Program'!M__);2,5;'Testing Program'!M__))-'Testing Program'!I__
#=MROUND(('Testing Program'!L29)*(AW6/100);2,5);IF(ISBLANK('Testing Program'!M__);2,5;'Testing Program'!M__))-'Testing Program'!I__
#=MROUND(('Testing Program'!L32)*(AW6/100);2,5);IF(ISBLANK('Testing Program'!M__);2,5;'Testing Program'!M__))-'Testing Program'!I__



Load
#=MROUND(('Testing Program'!E35)*(AW6/100);2,5);IF(ISBLANK('Testing Program'!F_);2,5;'Testing Program'!F_))-'Testing Program'!B_
#=MROUND(('Testing Program'!E37)*(AW6/100);2,5);IF(ISBLANK('Testing Program'!F_);2,5;'Testing Program'!F_))-'Testing Program'!B_
#=MROUND(('Testing Program'!E39)*(AW6/100);2,5);IF(ISBLANK('Testing Program'!F_);2,5;'Testing Program'!F_))-'Testing Program'!B_
#=MROUND(('Testing Program'!E41)*(AW6/100);2,5);IF(ISBLANK('Testing Program'!F_);2,5;'Testing Program'!F_))-'Testing Program'!B_
#=MROUND(('Testing Program'!E43)*(AW6/100);2,5);IF(ISBLANK('Testing Program'!F_);2,5;'Testing Program'!F_))-'Testing Program'!B_



Load
#=MROUND(('Testing Program'!L30)*(AW6/100);2,5);IF(ISBLANK('Testing Program'!M__);2,5;'Testing Program'!M__))-'Testing Program'!I__
#=MROUND(('Testing Program'!L31)*(AW6/100);2,5);IF(ISBLANK('Testing Program'!M__);2,5;'Testing Program'!M__))-'Testing Program'!I__
#=MROUND(('Testing Program'!L33)*(AW6/100);2,5);IF(ISBLANK('Testing Program'!M__);2,5;'Testing Program'!M__))-'Testing Program'!I__



Load
#=MROUND(('Testing Program'!E36)*(AW6/100);2,5);IF(ISBLANK('Testing Program'!F_);2,5;'Testing Program'!F_))-'Testing Program'!B_
#=MROUND(('Testing Program'!E38)*(AW6/100);2,5);IF(ISBLANK('Testing Program'!F_);2,5;'Testing Program'!F_))-'Testing Program'!B_
#=MROUND(('Testing Program'!E40)*(AW6/100);2,5);IF(ISBLANK('Testing Program'!F_);2,5;'Testing Program'!F_))-'Testing Program'!B_
#=MROUND(('Testing Program'!E42)*(AW6/100);2,5);IF(ISBLANK('Testing Program'!F_);2,5;'Testing Program'!F_))-'Testing Program'!B_
#=MROUND(('Testing Program'!E44)*(AW6/100);2,5);IF(ISBLANK('Testing Program'!F_);2,5;'Testing Program'!F_))-'Testing Program'!B_

What I need is to make a regex that will take the row number from the first part and replace it with the underscores.

Example:

#=MROUND(('Testing Program'!L28)*(AW6/100);2,5);IF(ISBLANK('Testing Program'!M__);2,5;'Testing Program'!M__))-'Testing Program'!I__

=>

#=MROUND(('Testing Program'!L28)*(AW6/100);2,5);IF(ISBLANK('Testing Program'!M28);2,5;'Testing Program'!M28))-'Testing Program'!I28

and

#=MROUND(('Testing Program'!E35)*(AW6/100);2,5);IF(ISBLANK('Testing Program'!F_);2,5;'Testing Program'!F_))-'Testing Program'!B_

=>

#=MROUND(('Testing Program'!E35)*(AW6/100);2,5);IF(ISBLANK('Testing Program'!F35);2,5;'Testing Program'!F35))-'Testing Program'!B35

What I have tried:

I haven't really come far. I've pasted the above text into the sample part of the test mode tab. I've played around with just some basic stuff, but I'm even not sure if I need to escape the text in the sample text part or not - or which characters need to be escaped. I've done a little bit of regex some twenty years ago, but mostly plain text from log files, and then mostly searching.

This is what I have for my regular expression, but running validate doesn't even match a single thing:

\#=MROUND\(\(\'Testing Program\'!([L]+L)(\d)\)*\(AW\d/100\);2,5\);IF\(ISBLANK\(\'Testing Program\'!M__\);2,5;\'Testing Program\'!M__\)\)-\'Testing Program\'!I__

I would be extremely grateful for any help you can give me. This will be a really nice learning experience for me.

Thanks!

Posted 9-Oct-18 11:13am

Lars Kvanum

Updated 9-Oct-18 12:12pm

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Lars Kvanum · Accepted Answer · 2018-10-09T12:12:00

Ok, it's about time I went to bed. I've currently come as far as using

(#=MROUND\(\('Testing Program'![L](\d+)\)\*\(AW\d\/100\);2,5\);IF\(ISBLANK\('Testing Program'!M)(__)(\);2,5;'Testing Program'!M)(__)(\)\)-'Testing Program'\!I)(__)

as my regular expression and

$1$2$4$2$6

as my replacement string. It does pretty much what I'm looking for for one of the cases, but might not be the most elegant solution...?...

-------------------
Ok. New day, new possibilities. I ended up with the following:
* Regex: (#=MROUND$\('Testing Program'![LlEe](\d+)$\*$AW\d\/100$;2,5\);IF$ISBLANK\('Testing Program'![MmFf])(__?)($;2,5;'Testing Program'![MmFf])(__?)(\)\)-'Testing Program'\![IiBb])(__?)
* Replacement: $1$2$4$2$6

If someone can help me out with a better solution I would love to hear it, but this should work for now :)