Regular Expressions and Patterns

[ElmerS]

Hi,

I have read a bit about the subject and even succeeded in accomplished a few tasks - but I wonder if there is a way to implement the RegExp on 2 different patterns in the same UDF, and if not - what is the shortest way to do so.

Assume A1 holds a string like: ABC(123)*DE#5 6+78_910
(There are 4 spaces between the 5 and the 6)

I want the UDF (with the RegExp object) to return: ABC 123 DE 5 678 910

Explenations:

a) When one of the 2 following characters was met it should be erased.
* +

b) When one of the 4 following characters was met it should be converted into a single space:
# _ ( )

c) Two, or more consecutive spaces should be converted in to a single space.
(Paragraph c can be achieved with TRIM)

To my little knowledge the above (a+b) need 2 different patterns which I was not able to implement.

I thought to nest 2 different UDFs but it seems not professional.

I even thought to add a second argument which, in this case will be 2 and declare 2 different pattern commands and by an IF check to use them Alternately - but I did not succeeded.

The basic UDF (works OK only for paragraph b ) looks like that:

Please Login or Register  to view this content.

Any help will be appreciated.

Elm

[squiggler47]

You could try :-

=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B8,"("," "),")"," "),"#"," "),"_"," "),"*",""),"+",""))

[squiggler47]

As a side note, I try not to use external code like vbscript and API calls unless necessary or the files are for my own use, as they are not available on all systems MAC/secure systems!

[DonkeyOte]

I think perhaps you mean for something akin to:

Please Login or Register  to view this content.

note the use of WorksheetFunction.Trim rather than the VBA Function - the latter will not remove superfluous spaces mid string unlike the former.

I have no doubt there are better ways to do the above - RegEx is not a strength of mine.

[squiggler47]

if VBA is a preference :-

Please Login or Register  to view this content.

[pike]

squiggler47 is right, always excel function first and vba last
just for the record a double take

Please Login or Register  to view this content.

[squiggler47]

As a side note I did some timings on the functions given, each cell with the function in was evaluated 1000 times:-

Squiggler's Formula 0.073
Pike's 2.264
Donkey's 2.409
Squiggler's VBA 0.192



Most of the extra time will be based in loading the regex library into memory!

[DonkeyOte]

Squiggler, if you have the experience I have answering the OPs questions you will know that he/she will be interested in only to those answering the specifics of the question - ie multiple regex patterns.

Efficiency & performance is rarely of interest, why remains something of a mystery but these days we just shrug and answer them to save others from the OP's generally terse responses.

If you want to amuse yourself run a search on threads started by OP.

They generally follow pattern of:

"I would to perform a contrived calculation without use of helpers, VBA ..."

If you want to pat yourself on the back for providing an efficient solution I won't stop you - but rest assured neither Pike nor myself suggested what we did based on efficiency grounds.

[squiggler47]

I thought in this case since the OP mentioned Professional in his question I might mention why it may be better to use VBA and no outside libraries. I wasnt aware when I wrote the code that it would turn out to be a factor of 10 faster!

[snb]

Please Login or Register  to view this content.

[DonkeyOte]

I would pretty much stake my house on the fact that any/all posts not related to the specifics of the question [multiple RegExp patterns] will be of little interest to Elm (that's not to say he/she won't appreciate them).

Regards efficiency - I would say that the slow nature of Regular Expressions is pretty well known so the timings aren't surprising. Goes without saying, however, that pending complexity of pattern(s) Reg Exp can be far simpler to code than the alternatives.

[squiggler47]

It doesnt work or give the correct result!

[squiggler47]

Yes! and I know the way the posts go on here, but surely we should at least point out that there are other, better ways even if they are ignored, so future readers of the threads may learn from it?

[DonkeyOte]

Perhaps, but with Elm I find I tend to post to the specifics in hope of ensuring damage limitation for others.

Below would be a few examples of the convoluted style questions I referred to:

http://www.excelforum.com/excel-gene...fix-digit.html
http://www.excelforum.com/excel-prog...-sentence.html
http://www.excelforum.com/excel-gene...a-formula.html
http://www.excelforum.com/excel-gene...ve-income.html
(note tone of OP reply etc)

I would say these are indicative of the majority of his/her posts.

For the record, I don't disagree with you - I'm a huge champion of efficiency over elegance but some times it just isn't worth the effort
And for others, there are better threads to learn from.

Thread hijack over.

No sign of the OP of course...

[shg]

And if she doesn't like the answer, doesn't respond at all.

http://www.excelforum.com/excel-gene...al-number.html

[ElmerS]

Thank you all.

The OP tries to have some life beside using/learning Excel and therefor the "delay" in his response.

All he was after is to learn the use of "multiple" patterns in one(!) UDF and look the kind of "story" you made out of it.

DO:
Your house is safe (for now).
Thanks for the UDF which works OK

Pike:
Thank you, as well. Your UDF also works fine.

To all others:
The OP has all the time in the world - therefore 2 seconds more / 2 seconds less to get the result in 1,000 cells means nothing to him.

All other suggestions (Nested Substitutes Formulas / VBA Using Replace and/or Substitute) are well known to the OP and therefor they do not belong to his "classroom".

DO got it right.
If the OP asks about "A" it means he wants to learn the way how "A" will produce the result and the OP supposes he has the full right for it.

The OP never forced anybody to response to his questions and with all due respect this is an open Excel forum where one can place any question he wants and mention some focusing/restricting the way to the solution.

Presenting other solutions, for other visitors sake is more than OK but it does not require any special attitude from the OPs side except of being polite and say: Thanks.

So, thank you all.

Oopsss, from the above you have learned a very important fact - which is the gender of the OP.

Elm

[DonkeyOte]

And now, squiggler47, you know what I was getting at...
ie don't waste too much of your freely donated time on these threads

As a rule a few of us try to answer these so others don't have to.

Elm, I thought you were going to try out other forums - no joy ?

[squiggler47]

One assumes since the word professional was used, the OP was striving to improve how the routine was written, as this is an Excel forum rather than a VBScript forum, it was thought to provide the knowledge in an excel based solution!

[royUK]

And if she doesn't like the answer, doesn't respond at all.

http://www.excelforum.com/excel-gene...al-number.html

No reply to this one either