Extracting name of person, Phone Number, and Address from a column of cells and paste into separate Cells

[pingme89]

I am using Excel 2010 and 2013.

I have a list of names, phone numbers, and addresses in 1 column.
Each cell contains a persons name, Telelphone number, and Home address.

John Doe (xxx) xxx-xxxx yyyy Street Name City Name Postal Code

Currently the list of information is in Column B.
I would like to remake this list upon clicking a Button that would copy the data onto a new sheet with Name under B column, Phone number in a C column, and Address in a D column column.

How can this be done with VBA? I have tried to make a loop but I am a VBA novice and not even sure what I am doing wrong.


Private Sub CommandButton1_Click()
Dim rCell As Range
Dim rRng As Range
Dim Pnumbloc As Integer
Dim PhoneNumb As String
Dim Name As String
Dim Address As String

Set rRng = Sheet1.Range("B1:B600")
For Each rCell In rRng.Cells
Pnumbloc = Search("(", rCell, 1)
Name = Mid(rCell, 1, Pnumb)
PhoneNumb = Mid(rCell, Pnumbloc, 14)
Address = Mid(rCell, Pnumbloc + 14, 45)
MsgBox Name & PhoneNumb & Address


Next rCell
End Sub

 

[Cindy Ellis]

It may be very difficult to separate this information if the different values are only separated by spaces, since both street names and city names can be multiple words. For example, John Doe (555) 555-5555 2345 New Main Street New York City 12345 can be "parsed" by a human, because we recognize the city name, and can separate it out from the rest of the information. Are there any consistent clues in the data (a comma, perhaps?) to distinguish street from city?

 

[pingme89]

It may be very difficult to separate this information if the different values are only separated by spaces, since both street names and city names can be multiple words. For example, John Doe (555) 555-5555 2345 New Main Street New York City 12345 can be "parsed" by a human, because we recognize the city name, and can separate it out from the rest of the information. Are there any consistent clues in the data (a comma, perhaps?) to distinguish street from city?

I actually don't need to separate the street from the city. I just need the whole address in one cell.

I actually tweaked my code that now works. See below.

Dim Counter As Integer
Dim Pnumbloc As Integer
Dim PhoneNumb As String
Dim Name As String
Dim Address As String
Dim EndName As Integer
Counter = 0
Do
Counter = Counter + 1
If Not IsEmpty(Cells(Counter, "B").Value) Then
Pnumbloc = InStr(Cells(Counter, "B").Value, "(")
EndName = Pnumbloc - 1
If IsEmpty(Cells(Counter, "C").Value) Then
Name = Mid(Cells(Counter, "B").Value, 1, EndName)
PhoneNumb = Mid(Cells(Counter, "B").Value, Pnumbloc, 14)
Address = Mid(Cells(Counter, "B").Value, Pnumbloc + 14, 65)
Cells(Counter, "B").Value = Name
Cells(Counter, "C").Value = PhoneNumb
Cells(Counter, "D").Value = Address
End If
End If
Loop While Counter < 800
Cells.Replace " Get directions?→", "", xlPart
Columns.AutoFit

The data is actually copied and pasted from the 411 online database. It has a link to a map on their site. The above code deletes " Get directions?→" and reformats the data with only the Name remaining in the B column. In the C column, The phone number is populated. In the D column, the whole address is populated.
I added another conditional statement to test if C column is populated or not. If the list has already been formatted and addtional entries were made to that list, the conditional statement ignores the rows where C column has already been populated and thus ensures that only unformatted cells would be processed.

If there is a better way, I wouldn't mind learning as this was an exercise to learn VBA.

Thanks for helping.