You can use Excel to process huge lists of URLs and generate useful reports such as a report on new links to a website. To accomplish this easily you will first need to extract domain names from the URLs.
A domain name is a unique website address that appears after www. and before the first slash (/) character in a web address. For example, in the URL https://codeigo.com/python/convert-csv-to-excel/, the domain name is codeigo.com.
In this tutorial, we will look at the following 3 methods that can be used to extract domain names from URLs:
- Use of an Excel Formula.
- Use Excel Find and Replace Feature with wildcards.
- Using Free Add-in with custom functions.
Method 1: Use of an Excel Formula
Step 1 – Select Cell B2 and key in the following formula.
1 |
=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)) |
Step 2 – Press Enter key and since the data is in a table, Excel automatically copies the formula to all the cells in Column B:
All the domain names have been extracted from the URLs.
To convert the formula results in Column B into values, proceed as follows:
Step 3 – Select any cell in Column B and press Ctrl + Space to select the cells in the Column.
Step 4 – Press Ctrl + C to copy the formula results to the Clipboard.
Step 5 – Right-click cell C2 and paste formulas as values:
The domain values will be pasted into Column C:
Explanation of the formula
1 |
=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 formula uses 4 functions:
- The IF function checks whether a condition is met, and returns one value if TRUE, and another value if FALSE.
- The ISERROR function checks whether a value is an error, for example, #N/A and returns TRUE or FALSE.
- The MID function returns the characters from the middle of a text string, given a starting position and length.
- The FIND function returns the starting position of one text string within another text string. It is case-sensitive.
Stepping through the formula
- FIND(“//www.”,A2) returns the starting position of the //www. string in the URL in Cell A2. If the string is not present a #N/A error is generated.
- The value generated by the FIND function is passed to the ISERROR function. If it is an error the ISERROR(FIND(“//www.”,A2)) returns TRUE otherwise it returns FALSE.
- TRUE means that //www. string was not found in the URL and therefore MID(A2,FIND(“:”,A2,4)+3,FIND(“/”,A2,9)-FIND(“:”,A2,4)-3) part of the formula is applied to extract the domain name.
- FALSE means that the //www. text string was found in the URL and therefore MID(A2,FIND(“:”,A2,4)+7,FIND(“/”,A2,9)-FIND(“:”,A2,4)-7) part of the formula is applied to extract the domain name.
Method 2: Use the Find and Replace Feature with a wildcard character
With the help Using the combination of the Find and Replace feature and a wildcard character will extract the domain names easily.
Step 1 – Select URLs and then press Ctrl + F to launch the Find and Replace dialog box, then click the Replace tab:
Step 3 – To remove the protocol before the URL, type *www. in the Find what box, leave the Replace with box blank and then press Replace All button:
Step 4 – Click OK on the popup Excel message box to accept the changes. All the protocols containing www. have been replaced with empty strings:
Step 5 – Repeat step 3 but now type in *:// in the Find what box. All the remaining protocols are removed:
Step 6 – To remove the paths after the domains, repeat step 3 but now type in /* in the Find what box. All the paths will be removed leaving you with only the domain names:
In this method, we first had to remove the protocol in the URL and then the path following the domain name.
Method 3: Use free SEO Excel Add-in
The easiest and quickest way to extract a domain name from a URL is to use an add-in. You can download it for free from the main page.
After you install the add-in, you can start using it.
Select the URLs you want to convert and click the Get Domain button in SEO >> Convert URLs.
Another way you can use this tool is by clicking the URL Converter button. After you click it, you can select one of the options:
Each option gives different results:
Using Custom Functions
If you prefer to use functions, you can use a function called:
1 |
=SEO.GETDOMAIN(url, [param]) |
The function takes one or two parameters. If there is no second parameter, the function works the same as:
1 |
=SEO.GETDOMAIN(url, 1) |
The additional optional parameters are 2, 3, and 4, and they work the same as the example used before.