If you are working with large lists of URLs that you need to strip down to the domain root, then this nifty Excel and Google Sheets formula might just be the time-saving tip that you're looking for.
Update:
The following formula will strip away protocols (https://,http://,www.) and the URI path. Use this instead.
Instructions:
Step 1, Assuming the list of URLs in column A, line 2. Paste the following formula into a cell of your choice. I generally create a new column to the right. For example B2. Drag the formula down to cover all the URLs listed on column A or use autofill.
Step 2, Next step is to strip away the protocols (https://,http://,www.). Leaving you with the first character of the domain name. For this, we are going to use the completed formula as covered in step 1. Again I generally create another new column. Let us use column C for this action.
Paste the formula below into C2 and drag it down to the end of the list of URLs.
That's it, you should have a complete list of URLs stripped right back down to the domain name root. Stripping away the protocols (https://,http://,www.) and the URI path (anything after domain.com/uri-here).
Comment below if you have a simpler method or formula. I would be keen to amalgamate this into a single formula.
Update:
The following formula will strip away protocols (https://,http://,www.) and the URI path. Use this instead.
Code:
=MID(SUBSTITUTE(A2,"www.",""),SEARCH(":",A2)+3,SEARCH("/",SUBSTITUTE(A2,"www.",""),9)-SEARCH(":",A2)-3)Instructions:
Step 1, Assuming the list of URLs in column A, line 2. Paste the following formula into a cell of your choice. I generally create a new column to the right. For example B2. Drag the formula down to cover all the URLs listed on column A or use autofill.
Code:
=LEFT(A2,FIND("/",A2,9))Step 2, Next step is to strip away the protocols (https://,http://,www.). Leaving you with the first character of the domain name. For this, we are going to use the completed formula as covered in step 1. Again I generally create another new column. Let us use column C for this action.
Paste the formula below into C2 and drag it down to the end of the list of URLs.
Code:
=MID(A2,FIND("//",B2)+2,LEN(B2)-FIND("//",B2)-1-(RIGHT(B2)="/"))That's it, you should have a complete list of URLs stripped right back down to the domain name root. Stripping away the protocols (https://,http://,www.) and the URI path (anything after domain.com/uri-here).
Comment below if you have a simpler method or formula. I would be keen to amalgamate this into a single formula.
