URL to IP Address – Intro

In Computer networking, IP address can be one of these 2 types & are represented in decimal/hexadecimal dotted format.

  • IPv4 – 32 bit (4 parts with 3 bits each)
    • Example: 123.456.789.123
  • IPv6 – 128 bit (8 parts with 16 bits each)
    • AB80:0010:0120:0010:ABF2:B4FF:FE2E:3419

Few Key Points:

  1. An IP address is assigned to network interface in Your Computer or Mobile device.
  2. Any communication device that uses internet protocol for data transfer should have one of the above numerical IP address.
  3. This ID is unique to the device.

PC to Website – How is it connected:

Every website that we browse in internet, will have the data stored in a Computer Server connected to the internet.

How would you find the geo location of the web server? find it in this link – Find Geo Location of an IP Address.

We type a website URL in the web browser & click ‘Go’. The URL is converted to an IP address with the help of Domain Name System (DNS) Servers.

Then our requests are routed to corresponding Server correctly & server responds us back with the requested web page.

How to find IP from Domain Name?

Internet browsers use DNS servers to convert  IP of a domain from its URL.

If you want a quick conversion, use the ping command in the command prompt as mentioned below.

C :\> ping www.officetricks.com

It will give an output as in below sample.

Pinging www.anywebsiteurl.com [xxx.xxx.xxx.xxx] with 32 bytes of data:
Reply from xxx.xxx.xxx.xxx: bytes=32 time=303ms TTL=45
Reply from xxx.xxx.xxx.xxx: bytes=32 time=326ms TTL=45
Reply from xxx.xxx.xxx.xxx: bytes=32 time=326ms TTL=45
Reply from xxx.xxx.xxx.xxx: bytes=32 time=304ms TTL=45

Ping statistics for xxx.xxx.xxx.xxx:
Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),
Approximate round trip times in milli-seconds:
Minimum = 303ms, Maximum = 326ms, Average = 314ms

These “abc.abc.abc.abc” will be a numeric address, if you have provided a valid website URL.

Get Website Server IP Address in Excel

To do this in excel we are going to automate the manual process explained above.

With the help of VBA and Shell execution, we are going to issue a PING command for a list of URL and convert the Website address to IP addresses.

  • Create a new workbook & enter a few website URLs in sheet1 stating from cell A1, A2… and so on.
  • Press Alt + F11 to get to the VB Editor. Copy paste the below code.
  • Execute the code by pressing F5.

This VBA code will output the converted IP addresses back to worksheet.

'          Visit our website for more Tips and Tricks                      '
'                ---- Officetricks.com ----                                '
'                                                                          '
Sub Find_Website_IP_Address()

    'Variable Declaration Section
    Dim objExec As Object
    Dim objWSH As Object
    Dim iWebURL As String
    Dim oPingdata As String
    iRow = 1

    'Create Object for Shell Script
    Set objWSH = CreateObject("WScript.Shell")
    'Get each URL from the Worksheet
    iWebURL = VBA.Trim(ThisWorkbook.Sheets(1).Cells(iRow, 1))
    While iWebURL <> ""
        'Execute PING command and capture the output
        Set objExec = objWSH.Exec("ping.exe " & iWebURL)
        oPingdata = objExec.StdOut.ReadAll()
        chking = VBA.InStr(1, oPingdata, "[", vbTextCompare)
        'Check whether Ping results in has IP address
        If VBA.InStr(1, oPingdata, "[", vbTextCompare) <> 0 Then
            Split1 = VBA.Split(oPingdata, "[")
            Split2 = VBA.Split(Split1(1), "]")
            'Domain ip lookup is completed. Store the IP in Worksheet
            ThisWorkbook.Sheets(1).Cells(iRow, 2) = Split2(0)
        End If
        iRow = iRow + 1
        iWebURL = VBA.Trim(ThisWorkbook.Sheets(1).Cells(iRow, 1))
    Set objWSH = Nothing
    Set objExec = Nothing
End Sub

This is not the only method possible to convert URL to IP address. But this is one of the simplest methods.

If you find this useful or have any alternate method, please leave a message in the comments section.