Once in a while, I find myself having to retrieve the root domain from a list of URLs that has been collected in an Excel file or a Google Sheets’ spreadsheet.
While scouring the web to look for such an information, I figured I needed to craft my own formula in both software that I could use and reuse every time I needed it. And today is the day where I finally share this formula so that you can all benefit from this time saving little hack.
How to obtain root domain from URL in Google sheets
For Google Sheets, I use this simple formula to extract the domain + subdomain of any URL I have in one cell.
This formula written below examine an URL in the cell A2 and extracts the domain of the URL by removing the inessential parts of the complete URL:
=REGEXREPLACE(REGEXREPLACE(A2,"(http(s)?://)?(www\.)?",""),"/.*","")In practice, this is how it looks like:
How to obtain root domain from URL in Microsoft Excel
For Microsoft Excel, I use this simple formula to extract the domain + subdomain of any URL I have in one cell.
This formula written below examine an URL in the cell A2 and extracts the domain of the URL by removing the inessential parts of the complete URL:
=IF(ISNUMBER(FIND("www.";A2));MID(A2;FIND("www.";A2)+4;IF(ISNUMBER(FIND("/";A2;9));FIND("/";A2;9);LEN(A2)+1)-FIND("www.";A2)-4);MID(A2;FIND("//";A2)+2;IF(ISNUMBER(FIND("/";A2;9));FIND("/";A2;9);LEN(A2)+1)-FIND("//";A2)-2))And this is how it looks like:
The formula itself is a bit chunky but this is Excel for you… Google Sheets is definitely more user-friendly and arguably as powerful as Microsoft Excel.
Conclusion
And that’s pretty much it for this article. This is a short one but I believe some of you might find it useful so I figured a quick and dirty article would not hurt. if you’re interested in learning more about how to use Google Sheets, I have a considerable amount of articles on the subject so I suggest you head over to our Google Sheets’ category page and just read on whatever you fancy :).
