Support free tutorials on code.broker by clicking on this ad ⬆

Get the domain name from an url on Excel or Google Sheet

Logo Excel Excel
Logo VBA VBA

By ,
Piblished the July 14, 2020

These are the formulas to extract the domain name from an URL in Excel or Google Sheet. To get the domain name and it exctension (domain.extension) from an URL, we are going to use a complex formula because the software Microsoft Excel don’t allow RegEx (Regular Expressions) in basic functions.However, you will be able to create your own functions in VBA to detect RegEx.

Here is what I am expecting to transform :

  • https://meilleurs-films.fr/contactez-nous/ => meilleurs-films.fr
  • http://www.squad-venture.com/escapegames/ => squad-venture.com
  • arthur-and-ashes.com/projects => arthur-and-ashes.com
  • https://www.impots.gouv.fr/portail/ => impots.gouv.fr

Be careful : here these formulas will also extract subdomains.

Extract domains from urls from excel
Extract a domain from url from excel

Get the domain name from an URL with Excel or Google Doc

The final formula that enable to extract domain names from an URL on Excel is :

=LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"www.",""),"http://",""),"https://",""),IFERROR(FIND("/",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"www.",""),"http://",""),"https://",""))-1,LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"www.",""),"http://",""),"https://",""))))

Google Sheet enables to use RegEx on its native functions, so it is easier to extract a domain name in this software. Here are the two formulas that enable to get a domain name from a web address :

=REGEXREPLACE(REGEXREPLACE(A2;"(http(s)?://)?(www.)?";"");"/.*";"")
=trim(REGEXEXTRACT(REGEXREPLACE(REGEXREPLACE(A2,"https?://",""),"^(w{3}\.)?","")&"/","([^/?]+)"))

Note : you can find the french formulas on the french translation of this article.

If you are curious on how to archieve it with VBA, just read the 3d part of this article.

Extract a domain name from an URL with VBA

If you are writing functions in Excel (macro) or if you are extracting data with VBA, here is a simple function to get the domain from an URL :

Function ExtractDomain(ByVal URL As String) As String
    If InStr(URL, "//") Then
        URL = Mid(URL, InStr(URL, "//") + 2)
    End If
    If Left(URL, 4) Like "[Ww][Ww][Ww0-9]." Then
        URL = Mid(URL, 5)
    End If
    ExtractDomain = Split(URL, "/")(0)
End Function
Subscribe
Notify of
0 Commentaires
Inline Feedbacks
View all comments

Did you like this tuto? Clic on the ad below ⬇ to support code.broker!