Title: To get url’s from a pasted list of Google.
Hello everybody, I need to get url’s from a pasted list of Google Search Page.
I am sure that is easy what I want, but on this post I did a big explanation to avoid any confusion.
I have pasted (and uploaded as attached files) 2 big pictures on this post (to help my explanation) and maybe you need to download them to be readable. Also I have attached 2 spreadsheets.
I have separated my explanation in different points, and every point is explained at the pasted pictures at the bottom of this post. So, this what I need:
Point 1.- I go to www.google.com and I search (for exemple) “dentist new york”.
Point 2.- I select all data (selection is showed in blue) and I copy it at clipboard.
Point 3.- Then I proceed to copy the selection with “Keep Source Formatting” on excel.
Point 4.- Data is pasted on excel.
Point 5.- Notice that on Google there are different kind of data. I have delimited with red rectangles (Google Advertisement and Google Maps) and I marked with green the data that I want.
Point 6.- So, I want to get the url’s that are on the green rectangle to the blue rectangle. It means, for example on cell “B72” there is a text that says “Pearl Dental NYC: Dentist Financial District, New York, NY” and is a link that goes to << http://www.pearldentalnyc.com/ >> and I want this url pasted on cell B4.
Again, on cell “B75” there is a text that says “Emergency Dentist NYC - 15 Photos - Oral Surgeons - East ...” and is a link that goes to << http://www.yelp.co.uk/biz/emergency-...nyc-new-york-7 >> and I want this url pasted on cell B5.
Beside this, also I need the data that is on “cell B14” (blue rectangle) where you can read “About 30,400,000 results (1.14 seconds)”. I need the number 30,400,000 pasted on cell B3.
Point 7.- Notice that there are different kinds of url’s and link’s. At red rectangle there are Google Advertisements and Google maps, and at the green rectangle there are the url’s that I want. So I want to avoid all data that appears on red rectangle.
Point 8.- I thought that one possible way to avoid the results that I have delimited on the red rectangles is that the results that I have delimited on green rectangle, below each cell there is another cell with a green colored text (RGB 0,102,33), I don’t know if it helps to get any formula or VBA code.
Point 9.- But also I would like a solution easy to extend with more columns (it means more keywords) and more rows (it means more Google results). For example, in the example showed on point 9 I have searched 3 keywords with 100 results every keyword. In this case I pasted “gym new york” on column B, “gym los angeles” on column C and “gym chicago” on column D. So, I would like a solution, formula or VBA code easy to adapt if any time I want to increase or decrease columns and rows.
I have uploaded the following documents (2 pictures and 2 excels):
Picture A - points 1 to 8.jpg: explaining points 1 to 8
Picture B – point 9.jpg: explaining point 9
Spreadsheet A – 1 keyword.xlsx: This is the spreadsheet that I have used to make the explanation for the points 1 to 8.
Spreadsheet B – 3 keywords.xlsx: This is the spreadsheet that I have used to make the explanation on point 9.
In summary this is what I need:
I would like a solution, formula or VBA code applied on (Spreedshet B – 3 keywords) to get:
1.- The pasted urls from Google as I have explained at the example on point 6.
2. Number of Google results as I explained at the example on point 6, and showed on cell B14.
3.- And also, as I have explained on point 9, an easy solution to adapt with more columns and/or rows.
PD: I know that there are a lot of programs that gets url’s from Google like “Free Monito for Google”. But, in any case, I want to do it by myself with excel. Although, if anybody want to suggest me any other software to gets the url’s that I need (to then be pasted into a spreedshet), this information will be welcome.
PD 2: I accept suggestions about another systems to get url’s from Google and pasted on an spreadsheet.
Thanks a lot in advance!
Picture A - points 1 to 8.jpg
Picture B – point 9.jpg
Bookmarks