Function Requested: Search, Look-up, and Copy/Paste from Google

[byeonyj]

Hello,

I have a large database with just about 2400 listings of addresses. It is in a single cell and most include the street name, then the city name followed by a comma.

What I would like to do (since there are 2400 of them), is find a way to somehow auto-populate the next column in the spreadsheet with each of the address's Counties. I have been sorting through the states, and tried filtering by cities but there are not enough redudant city entries that it is still very tough..

Ultimately, a function, programming, VBA code that do the following would be GREAT:
- recognize the last WORD in the row (which will be the name of city)
- search on Google
- search for the word "County" (as expedia almost always lists it in the preliminary google search window)
- take the word immediately BEFORE the word county (ex. Morris, Somerset, Monmouth)
- copy/paste back to original spreadsheet (preferably into the next column)

I am more than willing to find a different way to do this, but I have always been curious how you can connect a spreadsheet to a web search so that a macro/function automatically populates itself from the data found in the web.

My spreadsheet looks something like this, with the 2nd and 3rd column irrelevant.

NJ		38100		205.43		PAUL BLVD OFF OF MILL CREEK RD, STAFFORD TOWNSHIP
NJ		31062		204.09		PLEASANT VALLEY AVE., MOORESTOWN	Burlington
NJ		41215		204.08		18 LACKWANA PL, PASSAIC
NJ		67013		195.18		315 PASCACK RD, WESTWOOD	Bergen

<tbody>
</tbody><colgroup><col><col><col><col><col><col><col><col></colgroup>

So simple right?

Thanks in advance!

 

[aplasticfork]

I would attach this spreadsheet to your current workbook and search through this spreadsheet for the county based on the city. If you need additional coding for that, most of it you can find online. Let us know what you come up with and I'll help ya out more if needed.

Free ZIP Code Database - Free ZIP Code List