Sometimes you may need to collect domain names for some reason from a long list of web page URLs. This message will show you how extract domain names from URLs using Microsoft Excel. This can be useful if you plan to Disavow file for submission to Google. It’s pretty easy, and you can do it using an Excel formula.
Suppose you have a list of web page URLs from various websites and you only have to extract bare domain names (for example, thewindowsclub.com). If the file is small, you can do it manually. But it can take a long time if the list contains hundreds of URLs. Instead of putting in manual labor, you can take the help of this guide, where you will learn the process to remove extra parts of a URL and keep the domain name only.
Extract domain names from URLs using Excel
There are mainly two formulas that you should use. The first formula will allow you to obtain the full domain name; including www (for example, www.thewindowsclub.com). The second will delete www (for example, thewindowsclub.com) and only show you the domain name.
1]Extract domains with WWW
First, open the list of URLs in Microsoft Excel. If you have it in a .txt or .csv file, you can follow this tutorial to convert a text file into an Excel spreadsheet. Once you get the list, you need to specify a column where you want to display the domain names. For your convenience, you can create a column and name it “Domain” or something like that. After that select the first cell of the Field and enter this formula:
=MID(A2,FIND(":",A2,4)+3,FIND("/",A2,9)-FIND(":",A2,4)-3)
According to this formula, cell A2 is your source and the selected cell of Field The column should display the domain name with WWW.
2]Obtain domain names with WWW
Sometimes you will only need domain names for various purposes. If so, the following formula will do the trick.
=IF(ISERROR(FIND("//www.",A2)), MID(A2,FIND(":",A2,4)+3,FIND("/",A2,9)-FIND(":",A2,4)-3), MID(A2,FIND(":",A2,4)+7,FIND("/",A2,9)-FIND(":",A2,4)-7))
This extracts the domain name from one URL at a time. If you want to do the same with all URLs, you need to follow the next step.
For your information, these functions do not extract all the URLs at the same time because you must specify the cell number in these formulas. However, you do not need to enter the entire function each time. Instead, you can use your mouse to get the job done.
Select a cell in which you have applied the function to extract the domain name. You should see a small dot in the lower right corner. You have to click on this button and drag it to the bottom cells. You can see the result by pulling the point.
After obtaining the domain names, you may want to register it in another spreadsheet. The next problem is that you cannot copy domain names using Ctrl + C and Ctrl + V directly. Although you can do this to paste the domain names into Notepad or so, an error will be displayed to do the same in a spreadsheet.
To get rid of this problem, you need to select all the domain names you want to copy, select another column or spreadsheet, expand the Dough option in the Home tab and select Values under the Paste the values section.
That’s all. You can now use these values or domain names to perform other tasks. I hope this tutorial will help you.
To create a disclaimer file, you must now add the text “domain:” before all domains. This publication will show you how to add a prefix to a range of cells in Excel.