How to Extract Domain (TLD and Second Level Domains) Without Subdomains in VBA

Learn how to effectively extract the domain from email addresses in VBA, ignoring any subdomains for accurate sorting.
---
This video is based on the question https://stackoverflow.com/q/70690305/ asked by the user 'ClarkBHM' ( https://stackoverflow.com/u/17919558/ ) and on the answer https://stackoverflow.com/a/70690348/ provided by the user 'Tim Williams' ( https://stackoverflow.com/u/478884/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: How can I extract a domain (TLD and second level domains) but ignore subdomains?

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Extracting Domain: A Simple Guide to Ignore Subdomains in VBA

When working with email addresses in VBA, especially within Outlook, one common challenge developers face is how to effectively extract the main domain from an email address while ignoring the subdomains. This situation presents itself frequently when sorting or categorizing email data. If you’ve encountered this problem, you’re not alone. Let’s dive into how to solve it and ensure you get the correct domain for your email processing needs.

The Problem at Hand

Imagine you are processing multiple email addresses and you want to standardize your data by extracting only the main domain (the second level domain plus the top-level domain, or TLD) from each email address. However, subdomains can interfere with this extraction.

Sample Email Addresses and Expected Results

Email AddressExpected OutputA-xyz.comxyz.comA-subdomain.xyz.comxyz.comA-xyzN/AAs you can see from the table, two different structures exist: one with a standard domain and another with a subdomain. The goal is to ignore the subdomains and return only the main domain.

The Solution: VBA Code Implementation

The solution involves using a simple VBA macro that extracts the desired domain format from an email address efficiently. Below is a step-by-step guide on how to implement this.

Step 1: Create the Main Subroutine

In your VBA editor, you can start by creating a subroutine called DomainTester that will test different email addresses and print the results of the domain extraction.

[[See Video to Reveal this Text or Code Snippet]]

Step 2: Define the Domain Extraction Function

Next, you’ll create a function named Domain that takes an email address as input and returns the correct domain.

[[See Video to Reveal this Text or Code Snippet]]

Explanation of the Domain Function

Input Check: The function first checks if the address contains an '-' character. If it does not, the extraction cannot occur.

Splitting: The code splits the email address into parts using the '-' sign to isolate the domain.

Further Splitting: It then further splits the resulting domain part by the '.' character to access the various domain components (i.e., subdomain, second level domain, and top level domain).

Final Assembly: Using UBound, which returns the highest index of the array, the function concatenates the second level domain and TLD, ensuring subdomains are ignored.

Step 3: Test It Out

You can run the DomainTester subroutine and observe the output in the Immediate Window (accessible via Ctrl + G in the VBA editor). You should see the correct domains being printed, confirming that your solution works as intended.

Conclusion

By applying this simple VBA code, you can effectively extract the main domain from any email address without worrying about subdomains. This solution not only simplifies your data management but also ensures accurate sorting of your email data in Outlook.

Now you have a robust method for handling email domains in your VBA projects, so go ahead and implement it in your tasks to improve efficiency!