I found this to be incredibly useful when I needed to get coordinates for a set of hundreds of addresses. The original is from policeanalyst.com, with instructions updated for Excel 2013 from http://grindgis.com/.

The process involves adding a new item to your ribbon for developer tools, adding a block of Visual Basic code, referencing it to Microsoft XML, and saving it. When you use it in a workbook, you’ll summon up your new add-in and then use the new function, =MyGeocode().

Set Up The Developer Tab

First open a new blank workbook in Excel.Then go to the File menu, and at the bottom of the left column you’ll see “Options.” Click that and you’ll see this screen:

You’ll select “Customize Ribbon,” then check off the box next to “Developer.” Then click the OK button. You’ve got a new tab on your ribbon now. Click on it for the next step, which is…

Add Your VB Code

Try not to be daunted by this; unless you’re accustomed to code language it looks like gobbledygook. All you need to do is some copying and pasting. First (and this is a difference between Office 2013 and previous versions) click on the Visual Basic tab in the Developer ribbon:

After you click the Visual Basic icon, select the Insert dropdown, and pick Module.

Paste this code into the panel:

Function MyGeocode(address As String) As String
 Dim strAddress As String
 Dim strQuery As String
 Dim strLatitude As String
 Dim strLongitude As String
 strAddress = URLEncode(address)
 'Assemble the query string
 strQuery = "http://maps.googleapis.com/maps/api/geocode/xml?"
 strQuery = strQuery & "address=" & strAddress
 strQuery = strQuery & "&sensor=false"
 'define XML and HTTP components
 Dim googleResult As Object
 Set googleResult = CreateObject("MSXML2.DOMDocument.6.0")
 Dim googleService As Object
 Set googleService = CreateObject("MSXML2.XMLHTTP.6.0")
 Dim oNodes As MSXML2.IXMLDOMNodeList
 Dim oNode As MSXML2.IXMLDOMNode
 'create HTTP request to query URL - make sure to have
 'that last "False" there for synchronous operation
 googleService.Open "GET", strQuery, False
 googleService.send
 googleResult.LoadXML (googleService.responseText)
 Set oNodes = googleResult.getElementsByTagName("geometry")
 If oNodes.Length = 1 Then
 For Each oNode In oNodes
 strLatitude = oNode.ChildNodes(0).ChildNodes(0).Text
 strLongitude = oNode.ChildNodes(0).ChildNodes(1).Text
 MyGeocode = strLatitude & "," & strLongitude
 Next oNode
 Else
 MyGeocode = "Not Found (try again, you may have done too many too fast)"
 End If
 End Function
 Public Function URLEncode(StringVal As String, Optional SpaceAsPlus As Boolean = False) As String
 Dim StringLen As Long: StringLen = Len(StringVal)
 If StringLen > 0 Then
 ReDim result(StringLen) As String
 Dim i As Long, CharCode As Integer
 Dim Char As String, Space As String
 If SpaceAsPlus Then Space = "+" Else Space = "%20"
 For i = 1 To StringLen
 Char = Mid$(StringVal, i, 1)
 CharCode = Asc(Char)
 Select Case CharCode
 Case 97 To 122, 65 To 90, 48 To 57, 45, 46, 95, 126
 result(i) = Char
 Case 32
 result(i) = Space
 Case 0 To 15
 result(i) = "%0" & Hex(CharCode)
 Case Else
 result(i) = "%" & Hex(CharCode)
 End Select
 Next i
 URLEncode = Join(result, "")
 End If
 End Function

Add a Reference To It

In the same window, look under the Tools menu and pick References. Here you just want to make sure that Microsoft XML v6.0 is checked off. You might have to scroll down to find it.

Check the box, click OK.

Then save it (ctrl-S), as the type “Excel Add-In”. Give it a useful name — one you’ll recognize later. I called mine “Geocode.”

Start Using It

Then, when you’re ready to use it, go to File::Options::Add-Ins. Select the new Geocode Application Add-in and click OK.

For example, in a new spreadsheet enter an address in cell A1, such as

400 South Orange Ave., South Orange, NJ 07079

Then in another cell, add this code:

=MyGeocode(A1)

And as soon as you hit enter or tab, you should see this:

40.7425259,-74.2460794

You can then use the usual Excel tools to drag this across as many lines as you need.

For more details, see the grindgis.com page referenced above. Police Analyst doesn’t seem to have the original post up anymore, but they do have a lot of intriguing Excel/VB code examples to look through (VLOOKUP, anyone?)