In the current version of Excel, the TEXTAFTER function is a simple way to solve this problem.
Both approaches are explained below.
TEXTAFTER function
TheTEXTAFTER functionreturns the text that occursaftera given delimiter.
and returns “com”, which is the top-level domain.
As the formula is copied down, the other top-level domains are returned.
For more on TEXTAFTER, seeHow to use the TEXTAFTER function.
Legacy Excel
Older versions of Excel do not provide the TEXTAFTER function.
However, it operates in a series of small steps.
At the core, the formula uses theRIGHT functionto extract characters starting from the right.
Once the position is known, the RIGHT function is used to extract the TLD.
How does the formula know to replace only the last dot?
This is the clever and complicated part.
If no value is supplied forinstance_num, SUBSTITUTE will replaceall instancesofold_textwithnew_text.
However, if aninstance_numis provided, SUBSTITUTE will only replace that particular instance of old_text (i.e.
if 2 is provided, SUBSTITUTE will replace the second instance).
For example, the value in cell B5 is “https://www.domain.com”.
For example, =RIGHT(“apple”,3) returns “ple”.
LEN will also count characters in numbers, but number formatting is not included.
SUBSTITUTE Function
The Excel SUBSTITUTE function replaces text in a given string by matching.
When the text is not found, FIND returns a #VALUE error.
