有时您可能出于某种原因需要从一长串网页URL(URLs)中收集域名。这篇文章将向您展示如何使用Microsoft Excel从 URL 中提取域名(extract domain names from URLs )。如果您打算制作拒绝文件(Disavow File)以提交给Google ，这可能会很有用。这很容易，您可以在Excel公式的帮助下做到这一点。
假设您有一个各种网站的网页URL(URLs)列表，您只需要提取裸域名（例如，thewindowsclub.com）。如果文件很小，您可以手动执行此操作。但是，如果列表包含数百个URL(URLs)，则可能会很耗时。您可以借助本指南来代替人工劳动，在该指南中您将学习从URL中删除其他部分并仅保留域名的过程。
(Extract)使用Excel从(Excel)URL中(URLs)提取域名
您主要需要使用两个公式。第一个公式会让你得到完整的域名；其中包括www（例如 www.thewindowsclub.com）。第二个将删除www（例如，thewindowsclub.com）并只显示域名。
1] 使用 WWW 提取域
首先，在Microsoft Excel(Microsoft Excel)中打开URL列表。如果您将其保存在 .txt 或 .csv 文件中，您可以按照本教程将文本文件转换为 Excel 电子表格(convert a Text file into an Excel spreadsheet)。获得列表后，您需要指定要在其中显示域名的列。为方便起见，您可以创建一个列，并将其命名为“域”或类似名称。之后，选择域(Domain)列的第一个单元格，然后输入这个公式 -
=MID(A2,FIND(":",A2,4)+3,FIND("/",A2,9)-FIND(":",A2,4)-3)
根据此公式，A2 单元格是您的来源，并且Domain列的选定单元格应显示带有(Domain)WWW的域名。
2] 使用 WWW 获取域名
有时您可能只需要用于各种目的的域名。如果是这样，下面的公式将完成这项工作。
=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))
这一次提取一个URL(URL)的域名。如果您想对所有URL(URLs)执行相同操作，则需要执行以下步骤。
供您参考，这些函数不会一次提取所有URL(URLs)，因为您需要在这些公式中指定单元格编号。但是，您不需要每次都输入整个函数。取而代之的是，您可以使用鼠标来完成工作。
选择您应用函数以提取域名的单元格。您应该在右下角看到一个小点。您需要单击此按钮并将其拖向底部单元格。您可以在拉点时看到结果。
获取域名后，您可能希望将其保存在不同的电子表格中。下一个问题是不能直接使用Ctrl+C和Ctrl+V复制域名。尽管您可以将域名粘贴到记事本(Notepad)中，但在电子表格中执行相同操作会显示错误。
要解决这个问题，您需要选择所有要复制的域名，选择不同的列或电子表格，展开“主页”(Home )选项卡中的“粘贴”选项，然后在“(Paste )粘贴值(Paste Values)”部分下选择“值”。(Values)
就这样。现在您可以使用这些值或域名来做进一步的工作。希望本教程对您有所帮助。
要制作Disavow File，您现在需要在所有域前面添加文本“域： ”。(domain:)这篇文章将向您展示如何为 Excel 中的一系列单元格添加前缀。
How to extract domain names from URLs in Microsoft Excel
Sоmetimeѕ you might need to collect the domain names for some rеason from a long list of webpage URLs. This post will show you how to extract domain names from URLs using Microsoft Excel. This can be useful if you are planning to make a Disavow File for submission to Google. It is quite easy, and you can do that with the help of an Excel formula.
Let’s assume that you have a list of webpage URLs of various websites, and you need to extract only the naked domain names (e.g., thewindowsclub.com). If the file is small, you can do that manually. But it can be time-consuming work if the list contains hundreds of URLs. Instead of putting manual labor, you can take the help of this guide, where you will learn the process to remove additional parts from a URL and keep the domain name only.
Extract domain names from URLs using Excel
There are mainly two formulas that you need to use. The first formula will let you get the full domain name; which includes www (e.g., www.thewindowsclub.com). The second one will remove www (e.g., thewindowsclub.com) and show you the domain name only.
1] Extract domains with WWW
First, open the URL list 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 have got 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 as “Domain,” or something like that. After that, select the first cell of the Domain column, and enter this formula-
=MID(A2,FIND(":",A2,4)+3,FIND("/",A2,9)-FIND(":",A2,4)-3)
According to this formula, A2 cell is your source, and the selected cell of the Domain column should display the domain name with WWW.
2] Get domain names with WWW
Sometimes you might need only domain names for various purposes. If so, the following formula will do the job.
=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 of one URL at a time. If you want to do the same with all the URLs, you need to follow the following step.
For your information, these functions do not extract all URLs at once since you need to specify the cell number in these formulas. However, you do not need to enter the entire function every time. Instead of that, you can use your mouse to get the job done.
Select a cell where you applied the function to extract the domain name. You should see a small dot at the bottom-right corner. You need to click this button and drag it towards the bottom cells. You can see the result as you pull the dot.
After getting the domain names, you might want to save it in a different spreadsheet. The next problem is that you cannot copy the domain names using Ctrl+C and Ctrl+V directly. Although you can do that to paste the domain names in Notepad or so, an error will be displayed for doing the same in a spreadsheet.
To get rid of that problem, you need to select all the domain names that you want to copy, select a different column or spreadsheet, expand the Paste option in the Home tab, and select Values under the Paste Values section.
That’s all. Now you can use those values or domain names to do further jobs. Hope this tutorial would help you.
To make a Disavow File, you need to add the text “domain:” in front of all the domains now. This post will show you how to add a prefix to a range of cells in Excel.
