Extract Address and phone numbers from raw data.

[donlincolnmre2]

Hello

I have a spread sheet with bunch of raw data and its all in colum A, within that data there are 4 fields, Name, address, city state zip, phone number, I'm looking to extract that information from column A in to column, b, c, d, e, the macro suppose to grab that text from those 4 rows and just make 1 row, and placed that data in Column B, C, D and E, the macro should search for the entire sheet for those records.

Thanks.


here is the data.


Oklahoma l
Oregon l
Pennsylvania l
Rhode Island l
South Carolina l
South Dakota l
Tennessee l
Texas l
Utah l
Vermont l
Virginia l
Washington l
West Virginia l
Wisconsin l
Wyoming l
Lint, Michael G
2917 Ninth Ave
Sacramento, CA 95818-4046
(916) 446-4218
Find Neighbors l
Map and Driving Directions l
Printer-Friendly l
Send Listing to my Email l
Find Nearby Businesses l
Type: Land Line
SPONSORED LINKS
Listing date Nov. 2009
Local Information for Sacramento, CA
Population: 407,018 l

 

[hiker95]

donlincolnmre2,

Can we see another 30 to 50 rows of your raw data?

 

[VoG]

Duplicate http://www.mrexcel.com/forum/showthread.php?t=448306

 

[donlincolnmre2]

donlincolnmre2,

Can we see another 30 to 50 rows of your raw data?

The macro should only extract the data, if there is a phone number in column A

Here is the rest of the data

Arizona l
Arkansas l
California l
Colorado l
Connecticut l
Delaware l
District Of Columbia l
Florida l
Georgia l
Select -ഊHawaii l
Idaho l
Illinois l
Indiana l
Iowa l
Kansas l
Kentucky l
Louisiana l
Maine l
Maryland l
Massachusetts l
Michigan l
Minnesota l
List ViewMap View
Display: All (6) Home (5) Work (1)
Sort by:
Garroutte, Verdell
2529 S Coast Hwy, Apt E
Laguna Beach, CA 92651-3961
(949) 715-0922
Listing Detail
SPONSORED LINKS
Wilson, Rodney
2529 S Coast Hwy
Laguna Beach, CA 92651-3961
(949) 494-6939
Listing Detail
SPONSORED LINKS
Bottjer, Sherill L
2529 S Coast Hwy, Apt G
Laguna Beach, CA 92651-3961
(949) 494-6807
Listing Detail
SPONSORED LINKS
Gentry, Steve
Job title:
Owner
Company:
Gentry Plumbing
2529 S Coast Hwy
Laguna Beach, CA 92651-3961
Listing Detail
SPONSORED LINKS
Jones, Jerrod W
2529 S Coast Hwy, Apt G
Laguna Beach, CA 92651-3961
(949) 494-6807
Listing Detail
SPONSORED LINKS
Jones, Roger W
2529 S Coast Hwy, Apt G
Laguna Beach, CA 92651-3961
(949) 494-6807
Listing Detail
SPONSORED LINKS
Display: All (6) Home (5) Work (1)
Sort by:
Printer-Friendly Page
Phone Directory l
People Search l
Business Search l
Reverse Phone l
Reverse Address l
Area and ZIP Codes l
Site Sections
Telephone Directory
People Phone Number Search
Business Phone Number Search
Reverse Phone Directory
Reverse Address Finder
Area Code and ZIP Code Finder
Site Map
Regional Information
Phone Book by City and State
Phone Service by City and State
More Information
Reverse Phone Book
Search by Phone Number Lookups
Phone Service
Phone Listing
Online Phone Book
Privacy Central
Contact Us
Phone Books by State
Alabama l
Alaska l
Arizona l
Arkansas l
California l
Colorado l
Connecticut l
Delaware l
District Of Columbia l
Florida l
Georgia l
Select -ഊHawaii l
Idaho l
Illinois l
Indiana l
Iowa l
Kansas l
Kentucky l
Louisiana l
Maine l
Maryland l
Massachusetts l
Michigan l
Minnesota l
Mississippi l
Missouri l
Montana l
Nebraska l
Nevada l
New Hampshire l
New Jersey l
New Mexico l
New York l
North Carolina l
North Dakota l
Ohio l
Oklahoma l
Oregon l
Pennsylvania l
Rhode Island l
South Carolina l
South Dakota l
Tennessee l
Texas l
Utah l
Vermont l
Virginia l
Washington l
West Virginia l
Wisconsin l
Wyoming l
Phone Service by State
Alabama l
Alaska l
Arizona l
Arkansas l
California l
Colorado l
Connecticut l
Delaware l
District Of Columbia l
Florida l
Georgia l
Hawaii l
Idaho l
Illinois l
Indiana l
Iowa l
Kansas l
Kentucky l
Louisiana l
Maine l
Maryland l
Massachusetts l
Michigan l
Minnesota l
Mississippi l
Missouri l
Montana l
Nebraska l
Nevada l
New Hampshire l
New Jersey l
New Mexico l
New York l
North Carolina l
North Dakota l
Ohio l
Oklahoma l
Oregon l
Pennsylvania l
Rhode Island l
South Carolina l
South Dakota l
Tennessee l

 

[hiker95]

donlincolnmre2,

With all of your two posted data examples beginning in cell A1, before the macro:

Excel Workbook
	A	B	C	D	E
1	Oklahoma l
2	Oregon l
3	Pennsylvania l
4	Rhode Island l
5	South Carolina l
6	South Dakota l
7	Tennessee l
8	Texas l
Sheet1

After the macro:

Excel Workbook
	A	B	C	D	E
1	Oklahoma l	Lint, Michael G	2917 Ninth Ave	Sacramento, CA 95818-4046	(916) 446-4218
2	Oregon l	Garroutte, Verdell	2529 S Coast Hwy, Apt E	Laguna Beach, CA 92651-3961	(949) 715-0922
3	Pennsylvania l	Wilson, Rodney	2529 S Coast Hwy	Laguna Beach, CA 92651-3961	(949) 494-6939
4	Rhode Island l	Bottjer, Sherill L	2529 S Coast Hwy, Apt G	Laguna Beach, CA 92651-3961	(949) 494-6807
5	South Carolina l	Jones, Jerrod W	2529 S Coast Hwy, Apt G	Laguna Beach, CA 92651-3961	(949) 494-6807
6	South Dakota l	Jones, Roger W	2529 S Coast Hwy, Apt G	Laguna Beach, CA 92651-3961	(949) 494-6807
7	Tennessee l
8	Texas l
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Adding the Macro
1. Copy the below macro, by highlighting the macro code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub GetData()
' hiker95, 02/14/2010
Dim c As Range, firstaddress As String, NR As Long
Application.ScreenUpdating = False
With ActiveSheet
  .Range("A1").EntireRow.Insert
  If .Cells(2, 2) = "" Then
    NR = 2
  Else
    NR = .Range("B" & Rows.Count).End(xlUp).Offset(1).Row
  End If
  With .Columns(1)
    Set c = .Find("(*", LookIn:=xlValues, LookAt:=xlWhole)
    If Not c Is Nothing Then
      firstaddress = c.Address
      Do
        .Cells(NR, 2) = c.Offset(-3, 0)
        .Cells(NR, 3) = c.Offset(-2, 0)
        .Cells(NR, 4) = c.Offset(-1, 0)
        .Cells(NR, 5) = c
        NR = .Range("B" & Rows.Count).End(xlUp).Offset(1).Row
        Set c = .FindNext(c)
      Loop While Not c Is Nothing And c.Address <> firstaddress
    End If
  End With
  .Range("A1").EntireRow.Delete
  .Range("B1:E" & Cells(Rows.Count, 1).End(xlUp).Row).Columns.AutoFit
End With
Application.ScreenUpdating = True
End Sub

Then run the "GetData" macro".

 

[donlincolnmre2]

Hello hiker95

That worked great. Thanks a lot!!!