I recently read a blog post about using Excel for SEO (Search Engine Optimisation). It mentioned a function to extract a domain from a URL. The function was from Google docs, not Excel. So I wrote an Excel formula to extract the domain from their list of URLs.Below is a list of their URLs and the domains extracted. You can download the file at the bottom of the post.
The formula in cell B2 that has been copied down is
=SUBSTITUTE(SUBSTITUTE(MID(A2,SEARCH("//",A2)+2,SEARCH("/",A2,9)-SEARCH("//",A2)-2),"www.",""),"blog.","")
The MID function extracts everything between the // and the /.
The SEARCH functions find the // and the first /. The SUBSTITUTE function removes www. and blog. from the string. If there are other prefixes used you would need to add another SUBSTITUTE function for each one.
The SEARCH function returns the character position number of the first character being searched for, so I need to add 2 to the first SEARCH (as it searching for 2 characters) and subtract 2 from the last SEARCH for the same reason.
The formula requires that there is a // and /. If either or both are missing then the formula will return an error.
You might be able to use Flash Fill to do this as well, but it is a manual process not a formula.
Not sure how Power Query would go – I will have a play and see what I can do.
Please note: I reserve the right to delete comments that are offensive or off-topic.