How To Extract Domain Name From URL In Excel?

In Excel, extracting domain names from URLs can be a useful task when you want to analyse web data, categorize websites, or perform various other operations. By isolating the domain names, you can gain valuable insights and organize the information efficiently. In this article, we will guide you through the process of extracting domain names from URLs using Excel formulas and functions.

Method 1

Using Excel's Text Functions Excel provides a range of powerful text functions that can be utilized to extract domain names from URLs. Let's look at a step−by−step approach using the combination of functions.

Step 1

Prepare your data− Start by ensuring that your URLs are in a column in Excel. Let's assume that the URLs are in column A, starting from cell A3.

Step 2

Create a new column− In an empty column, let's say column C, enter the following formula in cell C3−

=SUBSTITUTE(SUBSTITUTE(MID(A2,SEARCH("//",A2)+2,LEN(A2)),"www.",""),"/","")

Note−

in the above formula, A3 indicates the cell which contains the webpage you want to get the domain name from, you can change it as you need.

Step 3

After entering the formula in cell C3, press the Enter key. Then, select cell C3 and use the fill handle (a small square in the bottom right corner of the cell) to drag it down over the range where you want to apply the formula. As you do this, Excel will automatically extract the domain names from each URL, populating the cells accordingly. You can refer to the screenshot below for visual guidance−

[Screenshot: Excel with formula in cell C3 and fill handle dragged down to extract domain names]

By following this step, you'll see the domain names extracted from each URL in the respective cells, allowing you to efficiently analyse and organize your web data.

Method 2

Using Custom VBA Function− If you prefer a more automated approach or need to extract domain names frequently, you can create a custom VBA function in Excel. Here's how you can do it−

Step 1

Open the Visual Basic Editor− Press "Alt + F11" to open the Visual Basic Editor in Excel.

Step 2

Insert a new module− From the menu, go to "Insert" and choose "Module" to insert a new module.

Step 3

Write the VBA function− In the module, write the following VBA code−

Function ExtractDomain(ByVal URL As String) As String
'Update 20140904
   If InStr(URL, "//") Then
      URL = Mid(URL, InStr(URL, "//") + 2)
   End If
   If Left(URL, 4) Like "[Ww][Ww][Ww0−9]." Then
      URL = Mid(URL, 5)
   End If
   ExtractDomain = Split(URL, "/")(0)
End Function

Step 4

Use the custom function− After writing the VBA code, close the Visual Basic Editor. You can now use the custom function " Extractdomain " in Excel, just like any other built−in function. For example, in cell C6, you can enter the formula−

Step 5

And then press Enter key, select the cell C6, and drag the fill handle down to the cells that you want to contain this formula, and all the domain names will be extracted from each webpage cell.

Conclusion

Extracting domain names from URLs in Excel can be accomplished using built−in text functions or by creating a custom VBA function. Both methods provide a convenient way to extract domain names and organize web data efficiently. Choose the method that suits your needs and start extracting domain names from URLs with ease.