In order to find out or extract the Top Level Domain (TLD) from a list of domains in Excel we use a formula that combines RIGHT function with SUBSTITUTE, LEN and FIND.
Top Level Domain (TLD) refers to the last part of the domain that appears after the dot ‘.’ sign. We can create a formula in Excel to extract the TLD by using RIGHT, SUBSTITUTE, LEN and FIND functions together.
=RIGHT(domain, LEN(domain) – FIND (“*” ,LEN (domain)–LEN (SUBSTITUTE(domain , “.” , “ “ )))))
The formula consists of 4 functions. Out of these, RIGHT extracts the characters that are available on the right side whereas the other functions help in finding out the TLD characters.
LEN function determines the length of the whole string or domain. SUBSTITUTE function replaces the last dot (before TLD) with an asterisk sign “*”. Then, FIND function finds or determines the position of asterisk sign“*”. Since TLD lies to the right of dot ‘.’ in a domain, so after finding the location of asterisk ‘*’, RIGHT is used to extract TLD.
This formula is quite complex, so let us understand it with the help of an example.
We take a list of email addresses as shown in column B and we extract the TLD in column C
Figure 1: Illustration of TLD extraction using RIGHT, FIND and LEN
To extract the TLD in C5, we enter the formula as shown in the figure.
The formula finds the total length of the domain and length of the domain removing dot ‘.” is also found. The latter is subtracted from the former. The resultant is the total number of dots in the domain. In the case of cell B5, it is 2.
Then, the SUBSTITUTE function replaces the second dot with asterisk “*”. FIND locates the position of asterisk sign. In this case, the position of the asterisk is 11. The formula then subtracts this result (11 in this case) from the total length of the domain.
Here, it was 15 characters so subtracting 11 from 14 we get 3, which finally results in “com”.
This was the tutorial on TLD, hope this would help you in the future.