On Mon, 24 May 2010 22:27:18 -0600, "Jim Berglund" wrote:

Ron. Thanks for the effort you put in on this. I would really like to
understand this more.

Is it checking each row to see if it meets the pattern, and if so, doing the
parsing and then building a new, 5-column list?

Yes. And if your row does not meet the format, it will do nothing.


What is the following line for?
Set myRegExp = CreateObject("vbscript.regexp")

That's called late binding, and it invokes another program which allows the use
of Regular Expressions in VBA. Regular Expressions are a powerful method of
manipulating strings.


Also, could you please help me understand the following? I've never seen any
code like it and I just don't have a clue how to interpret it...

myRegExp.Pattern = "^(\D+)\s+(.*)\s(CALGARY|MELBOURNE|SYDNEY)" &
"\s+([A-Z]{2})\s+(\w+)\s+(\(\d{3}\)\s+\d{3}-\d{4})$"

That is the pattern derived from the example you gave.

Everything was on one line so:

^ Start at the beginning of the line

(\D+) Capture everything that is not a digit into submatch 0. (In the
definition, the first digit was the beginning of the address). This will be
the name.

\s+ matches the space(s) between the end of the non-digits and the next
group.

(.*) capture everything, into submatch 1, up to the next match. This will
be the address.

\s matches the space between the end of the address and the next group

((CALGARY|MELBOURNE|SYDNEY) Short list of valid city names. This will need
to be extended to match your data. Capture into submatch 2.

\s+ matches the space(s) between the end of the city name and the province.

([A-Z]{2}) capture the next two capital letter abbreviation as the
Province into submatch 3

the rest matches the postcode, and phone number, again, according to the
pattern that was in the example you gave.




I tried it and it ran without any errors. But it didn't do anything.

It ran perfectly here using your line of sample data.

You have to enter and run it properly.

To enter this Macro (Sub), <alt-F11 opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code into the window that opens.

To use this Macro (Sub), FIRST *SELECT* the data you wish to parse. Then
<alt-F8 opens the macro dialog box. Select the macro by name, and <RUN.

If you did all that, and the macro did not do anything, then the data you ran
it against did not match, in format, what I had laid out. Or, possibly, your
newsreader wrapped a line and you did not copy it correctly into the VBEditor.
***Both I and JLGWhiz emphasized to you the importance of properly defining
your format.***

--ron