On Tue, 25 May 2010 17:36:54 -0600, "Jim Berglund" wrote:

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


There are two possible differences between the pattern in the first example and
what you've posted above.

1. The phone number is repeated and on a separate line. I cannot tell from
your posting if it is in the same cell (same row) or a different row.

2. The comma is a major difference. Is there always a comma? or is it
optional?

Two changes to be made in the regex:

1. Add the comma after the city names, but not within a capturing group.

2. Delete the "$" at the end, since there may be something after the phone
number (a duplicate phone number)

Result:

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

If the comma is optional, we indicate that in the regex by placing a "?" after
the comma:

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

This now works on all the examples you've posted.
--ron