Search code examples
excelurlexcel-formulatrim

Trim URL to ROOT/SUBDOMAIN with Excel

I need to trim URL's in Microsoft Excel to the root domain and to the subdomain.

A1 = Contains https://blog.example.com/page/
B1 = Should result in example.com
C1 = Should result in blog.example.com

Two formulas removing http, https, .www and PATH. The first version (B1) should also remove SUBDOMAIN.

I only have one formula right now:

=MID(SUBSTITUTE(A2;"www.";"");SEARCH(":";A2)+3;SEARCH("/";SUBSTITUTE(A2;"www.";"");9)-SEARCH(":";A2)-3)

https://example.com/page/page results in example.com
http://www.example.com/page/page results in example.com
http://blog.example.com/page/ results in blog.example.com
example.com/page results in #VALUE!
www.example.com/page results in #VALUE!

As you can see in the sample above I get good results. But it dosen't work without http or https. Also this version keeps subdomain.

Solution

  • Try this in B1,

    =SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(REPLACE(REPLACE(A1, 1, IFERROR(FIND("//", A1)+1, 0), TEXT(,))&"/", FIND("/", REPLACE(A1, 1, IFERROR(FIND("//", A1)+1, 0), TEXT(,))&"/"), LEN(A1), TEXT(,)), CHAR(46), REPT(CHAR(32), LEN(A1))), LEN(A1)*2)), CHAR(32), CHAR(46))

    .... and this in C1,

    =SUBSTITUTE(REPLACE(REPLACE(A1, 1, IFERROR(FIND("//", A1)+1, 0), TEXT(,))&"/", FIND("/", REPLACE(A1, 1, IFERROR(FIND("//", A1)+1, 0), TEXT(,))&"/"), LEN(A1), TEXT(,)), "www.", TEXT(,))

    replace_strip_url