Terrific! I think I am starting to understand what's been done. This is
really powerful stuff.

Is it possible to imbed the code in the program or does it have to be called
in as a macro each time?

I'll work it through again...

OK, I tried a number of variations, without success - even just copying data
into another spreadsheet and trying it.

Here is some of the actual data...

AAINEY J 1193 STRATHCONA DR SW CALGARY, AB T3H4S1 (403) 293-2671
(403) 293-2671
ABEN CONRAD 14 140 STRATHAVEN CI SW CALGARY, AB T3H2N5 (403) 249-1396
(403) 249-1396
ADAMS-CATHERWOOD T 71 STRATTON HILL RI SW CALGARY, AB T3H1W8 (403) 685-9699
(403) 685-9699
ADAMSON BRYCE P 330 555 STRATHCONA BV SW CALGARY, AB T3H2Z9 (403) 242-2000
(403) 242-2000
AIUTO ANTONIO 511 STRATHCONA ME SW CALGARY, AB T3H1X1 (403) 240-2055
(403) 240-2055
ALBINATI M J 115 STRADWICK RI SW CALGARY, AB T3H1G7 (403) 242-1028
(403) 242-1028
ALEXANDER RONALD R 120 STRATHBURY CI SW CALGARY, AB T3H1P9 (403) 249-2801
(403) 249-2801
AL-JANABY I 35 39 STRATHLE CM SW CALGARY, AB T3H5P8 (403) 256-5690
(403) 256-5690
ALLEN JACK 66 STRATHLEA CR SW CALGARY, AB T3H5A8 (403) 249-8289
(403) 249-8289
AL-SAYED M 11 STRATHLEA CM SW CALGARY, AB T3H5C5 (403) 663-8689
(403) 663-8689
ALSHAWI A 124 STRATHCONA CL SW CALGARY, AB T3H1L3 (403) 685-4070
(403) 685-4070
AMOOZEGAR NEZAM 7001 STRATHRIDGE GA SW CALGARY, AB T3H4R9 (403) 686-1116
(403) 686-1116
ANDA RICHARD 69 STRATHRIDGE CR SW CALGARY, AB T3H3R9 (403) 217-4776
(403) 217-4776
ANDERSON D B 80 STRADBROOKE RI SW CALGARY, AB T3H1T9 (403) 686-0702
(403) 686-0702
ANDERSON J 24 STRATHDALE CL SW CALGARY, AB T3H2K3 (403) 685-1424
(403) 685-1424

Would you please take another try and tell me what needs to change? (I think
the difference may be the comma after CALGARY)

Thanks again, Ron.
Jim

"Ron Rosenfeld" wrote in message
...
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