While doing SEO professionally I am always dealing with long lists of URLs. The best way to manage this is with Excel. Sometimes I need to take a list of URLs and parse them just to get the domain name.
For a while I kept re-writing the same formula each time I needed it. This time I’m going to share it- if only for myself, but someone else might be looking for it too.
Assuming that cell A2 contains the URL, if all you want is the domain name (with out the www), use this formula:
=IF(ISNUMBER(FIND("www.",A2)),MID(A2,FIND("www.",A2)+4,FIND("/",A2,9)-FIND("www.",A2)-4),MID(A2,FIND("//",A2)+2,FIND("/",A2,9)-FIND("//",A2)-2))
If you use a link report from MajesticSEO, who automatically removes the trailing slash at the end of homepages, you might want to use this version of the same code:
=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))
This should work whether or not the URL is HTTP or HTTPS. If a URL is the homepage of the website, this formula assumes that the homepage will end in a slash.
If you have a more efficient way to write this, or notice an error, please tell me in the comments.
7 comments about Excel formula to get domain name from URL
Good article. I need this right now for to see wich domains are worth targeting.
Hey David,
Don’t know if it’s more efficient or not but I just learned this trick the other day-
I wanted a column of just domains and then a separate column for each sub folder so I pasted all my urls in one column, hit text to columns, delimited, other: /
Once I had all that I also made pivot tables by subfolder and then subsequent categories under that, putting “subcategory” count as a value so I could see which were the main category sections and how big (and hence important?) each section of the site is.
Now I can quickly eyeball the site layouts from my excel
Great idea!
Hi David,
This macro is applicable when the complete URL starts with http://www.xyz.com/index, it returns xyz.com. Could you please provide me the macro for the URLs that doesnt start with ‘www’, i.e.,xyz.com/index … ?
1. This isn’t a macro, it’s a formula.
2. This works in the way you describe.
Hi David,
thanks for the formula, it works great for domain name with no subdomain. Would it be possible to have a formula to only get the host?
Hello David, Thanks for help us.
But, this not work ini URL like This Tidak bisa untuk URL :
htt*://somedomain.com/entries/order/site/www.otherdomain.com
#VALUE!
Thanks You