Solved Way to Automatically Look up Phone Numbers?

June 13, 2016 at 00:40:39
Specs: Windows 8.1, 8gb
I don't know if this is far fetched to do. But I have a list of 500+ addresses in a column in excell, all for the same city, and I have to copy and paste them into people search one at a time, then copy the phone number.

Is there a code or script to auto do this for me? So take the address in the cell, convert it into the link format "" open that in chrome, then copy the phone number and paste it back into the neighboring excel cell?

Perhaps once the yellow pages link is opened in chrome, in order to find the phone number, it searches for 3 digits followed by a dash, and then tells it, that's the phone number?

Idk, is this way to complicated to pull off? Here's a screenshot that explains it much better:


message edited by Joshexaminer

See More: Way to Automatically Look up Phone Numbers?

Reply ↓  Report •

June 13, 2016 at 01:44:05
This is possible, I did something similar a few years ago when building an Access database. I had to build a list of addresses within a given postal code/zip code.

What I did was, pass the past code to and using Microsoft WinHTTP Service, return the HTML code from the site. Then within that HTML code I extracted all the addresses. This was easy because amongst all the HTML the addresses were always prefixed by a standard pattern such as "<address>:" not exactly sure if this was it, but you get the idea. Then all I did was search for every instance of this, read the line and extract the date and enter into a listbox.

Unfortunately at my current place of work all WinHTTP calls are blocked so I cannot help implement this here. I can try it when I get time at home perhaps. What I would recommend is.


enter a few addresses, then right click on the webpage and select 'view source' within the code that appears search for the phone number and see if there is anything in common that prefixes the telephone number such as "<TelNo>:" if yes, then maybe we can use my code and modify it slightly to extract the phone numbers.

This might be over kill and perhaps someone has an easier option...but at least this is a start. You can google VBA WINHTTP and start looking into it.

Reply ↓  Report •

June 13, 2016 at 10:16:53
So there is a unique attribute in the html for the phone numbers, its: class="phone">925-736-1967</p>

So I'm not a coder at all, have no clue on this stuff. I found a guy on Freelancer Im going to hire, he said he's going to use Imacro, no idea what that is, but as long as it works


message edited by Joshexaminer

Reply ↓  Report •

June 13, 2016 at 13:08:13
✔ Best Answer
I looked at the source from the yellow pages and, like much of the html out there, it suffers from a lack of line-structure (sanely placed car.rets and line feeds). This fact, plus the abundance of special characters harmful to batch, pretty much precludes a pure batch solution. I made a vbscript to "fix" the line-feed problem since it's such a common affliction. You can use WGET to obtain the html, fix the line-feeds using crlf.vbs, then find the phone:

:: ---- begin batchscript YP.bat
@echo off & setlocal
rem this gets the html, into file YP, but the url still needs to be built from your source material
wget -O yp ""
rem next line puts a crlf in front of each "<" in the file.
cscript /nologo crlf.vbs yp
rem FINDSTR has to be used due to the dbl quotes which FIND can't deal with.
rem note that we could also put a replacement of dbl to sgl quotes into the vbscript,
rem but I wanted to keep the crlf.vbs minimal.
for /f "tokens=2 delims=>" %%a in ('findstr /i "class=\"phone\"" yp') do echo %%a

goto :eof
::---- end batchscript

'---- begin vbscript CRLF.vbs
set fso=createobject("scripting.filesystemobject")
fso.opentextfile(wscript.arguments(0),2).write x
'---- end vbscript

This solution is not "turn-key". You need to handle getting the data from the spreadsheet and building the wget statement. I have forgotten most of what little I knew about accessing the excel object, but you can always save the spreadsheet as text and use batch to work from that. If you go that route, and want me to help build the wget statement, just post or pmail me with a sample of the exported text. WGET can be found at sourceforge:
or just google "WGET WINDOWS". Then there's getting the phone back into the spreadsheet if that's what you intend to do.
ps: oops, didn't see your last statement before I posted this.
pps: oops again, mixed up city with zip. fixed in post. Imacros looks interesting, so thanks for that lead, and good luck.

message edited by nbrane

Reply ↓  Report •

Related Solutions

Ask Question