[ivory-search id=”5827″ title=”Default Search Form”]

After some head banging, i was able to come up with a solution for a universal google sheet formula that gets a domain from a URL even if the URL has sub domains. Most of the online solutions that i found dont work for subdomains.

Here is the formula. If your raw URL is present in A1, then the formula is

=concatenate(index(SPLIT(substitute(index(split(A1,"/"),2),"www.",""),"."),LEN(substitute(index(split(A1,"/"),2),"www.",""))-LEN(SUBSTITUTE(substitute(index(split(A1,"/"),2),"www.",""), ".", ""))),".com")

How does it work?

It splits by “/”, takes the second portion then counts the number of dots and splits by the count of dots. ( if there are two dots, it takes the second value of split, if only one dot is there, it takes the first value and finally concatenates .com to this value.