icon

Excel users often need to pull domain names from long URL lists. This task can be tricky without the right tools. Excel formulas can quickly extract domain names from URLs, saving time and effort.

These formulas work in all modern Excel versions. They can handle different URL types and formats. The process is simple once you know the steps.

Learning this skill can boost your Excel skills. It’s useful for many tasks like SEO work or data cleanup. With a few clicks, you can turn a long list of URLs into a clean list of domains.

  • Extract the domain with www. if it is present in a URL:
    =MID(A2,FIND(":",A2,4)+3,FIND("/",A2,9)-FIND(":",A2,4)-3)
  • Omit www. and get a pure domain name:
    =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))

Key Takeaways

  • Excel formulas can extract domain names from URLs
  • The process works in all recent Excel versions
  • This skill is helpful for SEO and data management tasks

Understanding Excel and URL Structures

Excel and URLs have key parts that work together. Knowing these parts helps extract domain names from web addresses.

Core Components of URLs

A URL has several pieces. The protocol comes first, like “http://” or “https://”. Next is the domain name. This can include a subdomain, like “www”.

The main domain follows. It’s the website’s name, like “google.com”. After that comes the path. This shows specific pages on the site.

Here’s a breakdown:

  • Protocol: https://
  • Subdomain: www
  • Domain: example.com
  • Path: /page/subpage

Domains are the key part for most tasks. They tell you which website you’re on.

Excel Spreadsheet Basics

Excel uses rows and columns to organize data. Each box is called a cell. Cells have names based on their column and row.

A1 is the first cell. It’s in column A, row 1. B2 is the cell in column B, row 2.

You can type text, numbers, or formulas in cells. Formulas start with “=” and can do math or work with text.

Excel has many built-in functions. These help with tasks like finding text in a cell. The RIGHT, LEFT, and MID functions are useful for working with URLs.

To extract domains, you’ll use these functions along with others. This lets you pull out just the part of the URL you need.

Step-by-Step Formulas to Extract Domains

Excel offers several ways to pull domain names from URLs. These methods range from basic text functions to more complex formulas and VBA code.

Basic Text Functions for Domain Extraction

The simplest way to get a domain name is to use the LEFT and FIND functions. Here’s how:

  1. Put your URL in cell A1.
  2. Use this formula in B1: =LEFT(A1,FIND(“/”,A1,9)-1)
  3. Copy the formula down for all URLs.

This pulls out everything before the first “/” after “http://”. It works for most URLs but may need tweaks for some.

Another option is the MID and FIND functions:

  1. Place your URL in A1.
  2. Use this formula in B1: =MID(A1,FIND(“://”,A1)+3,FIND(“/”,A1,9)-FIND(“://”,A1)-3)
  3. Copy down for all URLs.

This extracts the domain name more precisely by finding the text between “://” and the next “/”.

Advanced Techniques with SUBSTITUTE and MID

For trickier URLs, try this formula:

=MID(SUBSTITUTE(A1,”https://”,””),1,FIND(“/”,SUBSTITUTE(A1,”https://”,””))-1)

This does a few things:

  1. Removes “https://” from the URL
  2. Finds the first “/”
  3. Extracts everything before that “/”

It handles both “http://” and “https://” URLs. You can make it even better:

=IFERROR(MID(SUBSTITUTE(SUBSTITUTE(A1,”https://”,””),”http://”,””),1,FIND(“/”,SUBSTITUTE(SUBSTITUTE(A1,”https://”,””),”http://”,””))-1),A1)

This formula also works if there’s no “/” after the domain name.

Automating with Excel VBA Code

For lots of URLs, VBA can speed things up:

  1. Press Alt+F11 to open the VBA editor.
  2. Insert a new module.
  3. Paste this code:
Sub ExtractDomains()
    Dim cell As Range
    For Each cell In Selection
        cell.Offset(0, 1).Value = Mid(cell.Value, InStr(cell.Value, "://") + 3)
        cell.Offset(0, 1).Value = Left(cell.Offset(0, 1).Value, InStr(cell.Offset(0, 1).Value, "/") - 1)
    Next cell
End Sub
  1. Run the macro on your URL column.

This code loops through each cell, finds the domain, and puts it in the next column. It’s fast for big lists of URLs.

Frequently Asked Questions

Excel offers several ways to extract domain names from URLs. These methods use formulas and functions to parse web addresses and email addresses.

How can I extract just the domain from a complete URL in Excel?

To get the domain from a URL, use this formula:

=TEXTBEFORE(TEXTAFTER(A1,”//”),”/”)

Replace A1 with the cell containing your URL. This extracts the domain name between “//” and the next “/”.

What is the method to obtain only the main domain from a list of URLs in Excel?

For a list of URLs, use this formula:

=MID(A1,FIND(“://”,A1)+3,FIND(“/”,A1,FIND(“://”,A1)+3)-FIND(“://”,A1)-3)

Put this in the cell next to your first URL. Then copy it down for all URLs in your list.

Is there an Excel formula that can separate the domain from an email address?

Yes. To get the domain from an email, try this formula:

=RIGHT(A1,LEN(A1)-FIND(“@”,A1))

A1 should be the cell with the email address. This pulls everything after the “@” symbol.

How to parse and extract the root domain from a FQDN in Excel?

To get the root domain from a fully qualified domain name:

=TEXTBEFORE(TEXTAFTER(A1,”.”),”.com”)

Change “.com” to match your domain ending. This works for most common domain types.

What steps should be followed to extract a domain from a subdomain using Excel?

Follow these steps to remove subdomains:

  1. Use this formula: =TEXTAFTER(A1,”.”)
  2. If needed, repeat the formula to remove multiple subdomains
  3. Copy the result to a new column using Paste Special > Values

Can Excel be used to automatically retrieve website URLs from a list of company names?

Excel can’t do this on its own. You’d need to:

  1. Use a web search API
  2. Set up Power Query to fetch data
  3. Or manually search and input URLs

These options need extra setup or paid services.

Similar Posts