[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.

URL to domain google sheets

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.

By Muthali Ganesh

I am an engineer wih a masters in business administration from Chennai, India. I love discovering and sharing hacks.