Return Business Name from Address in Excel

[KelvinBruns]

Hello All:
I am working on a project with several thousands rows. I have the addresses for doctors in the state of Florida and need to find the Business Name of the address that is listed. I do not know if the best route is to use a web query or VBA code. The address are in the following format.

Cell E6 | Cell F6 | Cell G6 | Cell H6 | Cell I6
Address Line 1 | Address Line 2 | City | State | Zip Code
601 EAST ROLLINS STREET | | ORLANDO | FL | 32803

Any help would be greatly appreciated! I do not have the time to manually search 500,000 Rows. Another question will be how to get the Business Website from the Business Name.

Thanks,

 

[mole999]

if you have a consistent and unique value then a VLOOKUP up could work so 601 EAST R i.e =TRIM(LEFT(CELL,10) would get you an identifier

 

[KelvinBruns]

I meant that I do not have time to do a manual search for all 500,000 rows of addresses that I have.
I would need to somehow be able to google the address and return the Business Name.

 

[mole999]

the Google API might be able to do that

 

[brawnystaff]

This is a tough one, as one address might have multiple people in it. Furthermore, even if only one person at address, may list both their personal name and business name. Also, sounds like you are trying to search the web globally, instead of getting the info from one site.

I am thinking you need to narrow done your search. If your search is limited to doctors/physicians, then since November 1, 2008 to present, all physicians must have an National Practitioner Identifier (NPI), which is issued by the U.S. Dept of Health and Human Services, if they wish to bill insurance companies The website to lookup a physicians NPI is as follows:

https://npiregistry.cms.hhs.gov/

The page does have instructions to query through their API, which you could through Excel via JSON. The only issue I foresee, in addition to the ones above, would be syntax problems with the address (e.g. 123 South Main Street, or 123 S. Main St.) There is also problems with individuals putting the second line of the Business Address in with the first (e.g. 123 S. Main St, Suite 123 instead of putting the Suite number is the second address line).

Instead of messing with the online database, recommend you download the entire NPI registry "flat file" at the following URL:

NPI Files
Download the file and unzip, which will reveal a CSV file with over 5 million rows. Since it contains over 5 millions rows of data, you will have to use Power Query (Get & Transform in Excel 2016) to import into Excel. When importing, you will need to use the criteria "Provider Business Practice Location Address State Name = FL" to import only physicians from Florida. You can also selectively import only the columns you need vs. all of them (something like 300+ columns). This should give you a spreadsheet with a few hundred thousand rows.

Once you have your spreadsheet with only Florida providers, use VLOOKUP or Index/Match to return the names of the physicians for the addresses you have (note: Entity Type Code "1" is for individuals, "2" is for businesses)

Hope this helps...

 

[KelvinBruns]

Wow brawnystaff that is a solid idea. What is the URL to download the NPI Registry "Flat File" and also can the Power Query be used in Excel 2013 (Version that i am using)? I appreciate the assistance with this question!
Thanks!

 

[brawnystaff]

Link to NPI flat file is as follows:

NPI Files

Link to PowerQuery is as follows:

https://www.microsoft.com/en-us/dow...lationId=855b29cf-2fc3-4d2c-9fb5-87336171f352

You can use the PowerQuery addin to selectively import the Florida NPIs. However, since I already had the flat file and Excel 2016 (has Power Query built in), I was able to extract the Florida NPIs to a spreadsheet. It totaled 318,428 rows. I uploaded to my Google Drive and shared at the following URL:

https://drive.google.com/open?id=0BweyQNyritHaZGxWT3pVWlg0Qkk

Columns AC to AG has the business address, E to G business or physician name.

 

[KelvinBruns]

Thanks for the help!