Parsing a String - Call to Ron Rosenfeld

[Jim Berglund]

Ron Rosenfeld kindly gave me the following:

Sub ParseAddr()
Dim myRegExp As Object, myMatches As Object
Dim rg As Range, c As Range
Dim i As Long

With ActiveSheet
Range("A:A").Select
Set rg = Selection
Set myRegExp = CreateObject("vbscript.regexp")
myRegExp.Pattern = "^(\D+)\s+(.*)\s(CALGARY|MELBOURNE|SYDNEY)" &
",?\s+([A-Z]{2})\s+(\w+)\s+(\(\d{3}\)\s+\d{3}-\d{4})"

For Each c In rg
If myRegExp.test(c.Text) = True Then
Set myMatches = myRegExp.Execute(c.Text)
For i = 0 To 5
c.Offset(0, i + 1) = myMatches(0).submatches(i)
Next i
End If
Next c
End With
End Sub

It works fine against the following data, creating 5 columns with the Name,
Address, City, Prov, and Telephone Number, respectively.

ABACO CHRISTIAN P 32 PATTERSON ME SW CALGARY, AB T3H2C7 (403) 238-2039
begin_of_the_skype_highlighting (403) 238-2039
end_of_the_skype_highlighting

'Just one tweak needed...

I'd like to separate the last name from the remainder of the name (which may
be anything from "P" to "DR QUINCY R & JULIE" and create 6 columns.

Would someone (preferable Ron), please explain to me how to do this?

Thanks, once again

Jim Berglund

 

[Jim Berglund]

Thanks again, Ron.
By the way, if the city is a variable, City, and not a selection from a list
of known cities, could this be handled?

Jim

Ron Rosenfeld kindly gave me the following:

Sub ParseAddr()
Dim myRegExp As Object, myMatches As Object
Dim rg As Range, c As Range
Dim i As Long

With ActiveSheet
Range("A:A").Select
Set rg = Selection
Set myRegExp = CreateObject("vbscript.regexp")
myRegExp.Pattern = "^(\D+)\s+(.*)\s(CALGARY|MELBOURNE|SYDNEY)" &
",?\s+([A-Z]{2})\s+(\w+)\s+(\(\d{3}\)\s+\d{3}-\d{4})"

For Each c In rg
If myRegExp.test(c.Text) = True Then
Set myMatches = myRegExp.Execute(c.Text)
For i = 0 To 5
c.Offset(0, i + 1) = myMatches(0).submatches(i)
Next i
End If
Next c
End With
End Sub

It works fine against the following data, creating 5 columns with the
Name,
Address, City, Prov, and Telephone Number, respectively.

ABACO CHRISTIAN P 32 PATTERSON ME SW CALGARY, AB T3H2C7 (403) 238-2039
begin_of_the_skype_highlighting (403) 238-2039
end_of_the_skype_highlighting

'Just one tweak needed...

I'd like to separate the last name from the remainder of the name (which
may
be anything from "P" to "DR QUINCY R & JULIE" and create 6 columns.

Would someone (preferable Ron), please explain to me how to do this?

Thanks, once again

Jim Berglund

How do we know which name is the "last name" ?

In your earlier examples, it seemd to be the first word. If that is
the case, you can do it by modifying the Regex to split out the first
word from the rest.

Note in the following I used a somewhat different method to select the
range to parse, so as to reduce the number of cells to be processed.

======================================
Option Explicit
Sub ParseAddr()
Dim myRegExp As Object, myMatches As Object
Dim rg As Range, c As Range
Dim i As Long

Set rg = Range("A1") 'first cell in column
Set rg = Range(rg, Cells(Cells.Rows.Count, rg.Column).End(xlUp))
rg.Offset(0, 1).Resize(columnsize:=7).ClearContents

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

For Each c In rg
If myRegExp.test(c.Text) = True Then
Set myMatches = myRegExp.Execute(c.Text)
For i = 0 To myMatches(0).submatches.Count - 1
c.Offset(0, i + 1) = myMatches(0).submatches(i)
Next i
End If
Next c

End Sub
======================================

 

[Jim Berglund]

How about if the city is entered through an input box as the string variable
"City"? Each user will be located in a different city, and each time a list
is being parsed, it will only contain records for that city.

Jim

 

[Jim Berglund]

....or what if we entered the city from an input box, determined the length
of the name and counted back from the comma?
Jim

 

[Jim Berglund]

Sorry, Ron. Let me go back. The application is to manage the impact of Do
Not Call Lists. The data changes daily. We typically work from reverse
phone directories, which list all the numbers in an area code. I I'm trying
to create a list of callable numbers along with whatever contact information
is available in the Reverse Phone Books.

The data in the RPB's is not consistent - but it is consistent enough that
the formula you created works for the data for Calgary. To be generically
useful, I need to be able to allow users to just input the city for their
Area Code. The rest of the data is available...

So, I can use a variable, or it will also work if we remove the reference to
the city - totally. Either will be fine.

I'd like to use a variable - if only to see how it's handled. But if it
can't be, would you please redo the formula and remove the city reference?

Thanks again
Jim

 

[Jim Berglund]

Exactly! That's perfect.
Thanks so much for staying with me on this one, Ron. I can now substitute
"SAN FRANCISCO" and it works. I'm delighted.
Jim

Sorry, Ron. Let me go back. The application is to manage the impact of Do
Not Call Lists. The data changes daily. We typically work from reverse
phone directories, which list all the numbers in an area code. I I'm
trying
to create a list of callable numbers along with whatever contact
information
is available in the Reverse Phone Books.

The data in the RPB's is not consistent - but it is consistent enough that
the formula you created works for the data for Calgary. To be generically
useful, I need to be able to allow users to just input the city for their
Area Code. The rest of the data is available...

So, I can use a variable, or it will also work if we remove the reference
to
the city - totally. Either will be fine.

I'd like to use a variable - if only to see how it's handled. But if it
can't be, would you please redo the formula and remove the city reference?

Thanks again
Jim

Well, you could input a city name and incorporate it into the pattern.

e.g.:

==================================
Option Explicit
Sub ParseAddr()
Dim myRegExp As Object, myMatches As Object
Dim rg As Range, c As Range
Dim i As Long
Dim sCity As String

sCity = InputBox("Name of City: ")

Set rg = Range("A1") 'first cell in column
Set rg = Range(rg, Cells(Cells.Rows.Count, rg.Column).End(xlUp))
rg.Offset(0, 1).Resize(columnsize:=7).ClearContents

Set myRegExp = CreateObject("vbscript.regexp")
myRegExp.ignorecase = True
myRegExp.Pattern = _
"^(\D+?)\s(\D*)\s*(.*)\s(" & sCity & "),?" _
& "\s+([A-Z]{2})\s+(\w+)\s+(\(\d{3}\)\s+\d{3}-\d{4})"

For Each c In rg
If myRegExp.test(c.Text) = True Then
Set myMatches = myRegExp.Execute(c.Text)
For i = 0 To myMatches(0).submatches.Count - 1
c.Offset(0, i + 1) = myMatches(0).submatches(i)
Next i
End If
Next c
End Sub
==================================

It would then only process those lines that had the city name
"Calgary".