Extract Domain from URL in Excel

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:

1. Use of an Excel Formula.
2. Use Excel Find and Replace Feature with wildcards.
3. 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.

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

This formula uses 4 functions:

1. The IF function checks whether a condition is met, and returns one value if TRUE, and another value if FALSE.
2. The ISERROR function checks whether a value is an error, for example, #N/A and returns TRUE or FALSE.
3. The MID function returns the characters from the middle of a text string, given a starting position and length.
4. The FIND function returns the starting position of one text string within another text string. It is case-sensitive.

Stepping through the formula

1. 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.
2. 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.
3. 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.
4. 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:

The function takes one or two parameters. If there is no second parameter, the function works the same as:

The additional optional parameters are 2, 3, and 4, and they work the same as the example used before.