Prev: Using Color as Criteria in Countif Function
Next: Create a list with multiple criteria
From: Jim Berglund on 25 May 2010 19:36
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" <ronrosenfeld(a)nospam.org> wrote in message
news:ul7nv59mr7r28688hbscpphankmrilrbje(a)4ax.com...
> On Mon, 24 May 2010 22:27:18 -0600, "Jim Berglund" <jazzzbo(a)shaw.ca>
> 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

From: Ron Rosenfeld on 25 May 2010 20:28
On Tue, 25 May 2010 17:36:54 -0600, "Jim Berglund" <jazzzbo(a)shaw.ca> 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
From: Ron Rosenfeld on 25 May 2010 20:29
On Tue, 25 May 2010 17:36:54 -0600, "Jim Berglund" <jazzzbo(a)shaw.ca> wrote:

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

I don't know what you mean by this question.

What are you considering to be "in the program"?

Regular expressions come in many flavors, and can be used in a variety of
languages.
--ron
From: Jim Berglund on 25 May 2010 21:29
The phone number is repeated in each row, and
the comma is always there.

Thanks again,
Jim

"Ron Rosenfeld" <ronrosenfeld(a)nospam.org> wrote in message
news:pfqov5pnpc4ej99ec7oo4ra3q2ad6jvgkc(a)4ax.com...
> On Tue, 25 May 2010 17:36:54 -0600, "Jim Berglund" <jazzzbo(a)shaw.ca>
> 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.

The phone number is repeated in each row, and
the comma is always there

>
> 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

From: Ron Rosenfeld on 25 May 2010 22:06
On Tue, 25 May 2010 19:29:04 -0600, "Jim Berglund" <jazzzbo(a)shaw.ca> wrote:

>The phone number is repeated in each row, and
>the comma is always there.
>
>Thanks again,
>Jim

And what happened when you changed the regex as I suggested? It should work,
unless there's some other difference not accounted for,
--ron
First  |  Prev  |  Next  |  Last
Pages: 1 2 3
Prev: Using Color as Criteria in Countif Function
Next: Create a list with multiple criteria