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

pingme89

Board Regular
Joined
Jan 23, 2014
Messages
149
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
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Cindy Ellis

MrExcel MVP
Joined
Jun 9, 2006
Messages
1,802
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

Board Regular
Joined
Jan 23, 2014
Messages
149
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.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,469
Messages
5,596,329
Members
414,055
Latest member
mcarduner

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top