Re: Regular expressions for replacements in Excel?

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Shannon Jacobs (shanen_at_my-deja.com)
Date: 12/24/04 

Date: Fri, 24 Dec 2004 11:47:40 +0900

Jezebel wrote:
> If you are dealing with just one column of the spread***, it might
> be easier to copy it into a Word document, make the changes there,
> and copy it back. Word's Find and Replace has the regular expression
> functions built in (check the 'Use Wildcards' checkbox), so you can
> see what's going on and you don't need to screw around with coding at
> all.

The Word wildcards seem to be much more limited than real regular
expressions, so I had already eliminated that option. I haven't listed all
of my transformations here, but a number of them are somewhat trickier, so
that won't work. Your suggestion with the loop simplification is good, but I
don't think the Call itself is really causing much of the overhead, but you
are right that I should move it to one level. The other idea about using the
range.replace doesn't seem to apply here.

(In addition, I admit that part of my motivation is to learn about macro
programming specifically within Excel, since my coworkers already use it a
lot (though none of them are into regular expressions).)

For now, I'll just note the mistake and fix for the problem I commented on
last time:

This is the incorrect code:
>> ' remove fronts of image tags, but save the last word
>> aRegExp.Pattern = "<img src ?= ?\./[\w+/]*(\w+)"
>> theLine = aRegExp.Replace(theLine, "\1")

This is correct:
>> ' remove fronts of image tags, but save the last word
>> aRegExp.Pattern = "<img src ?= ?\./(\w+/)*"
>> theLine = aRegExp.Replace(theLine, "")

Typically dumb typo with typical fatal results in a RegExp. Within a
bracketed class of course the \w picked up the rest of the string, whether
or not it ended in the slash that I intended to require. By ending the match
after the last slash, I didn't have to worry about explicitly preserving the
last word.

I still don't understand why the \1 didn't work as expected, but just for
grins, I tried ye olde $1, which produced really weird results... This may
be a future problem, since I think that one of the later transformations is
not just a simple deletion, but will require submatch reference in the
replacement... Right now I'm just massaging the line breaks, which is kind
of a nuisance since I still can't get the MultiLine parameter to work as I
had hoped. If that would only work, I should be able to slurp the next major
transformation in one step or two...

Quantcast