使用Excel将URL修改为ROOT / SUBDOMAIN [英] Trim URL to ROOT/SUBDOMAIN with Excel

问题描述

我需要将Microsoft Excel中的URL修剪到根域和子域。

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

A1 =包含 https://blog.example.com/page/

B1 =应该导致 example.com

C1 =应该导致 blog.example.com

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

两个公式删除http，https，.www和PATH。第一个版本（ B1 ）也应该删除SUBDOMAIN。

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

我现在只有一个公式：

= MID（SUBSTITUTE（A2; WWW; ）; SEARCH（ ：; A2）3; SEARCH（ /;替代（A2; WWW， ）; 9） - SEARCH（：; A2）-3）

https://example.com/page/page 结果 example.com

http://www.example.com/page/页面导致 example.com

http://blog.example.com/page / 结果 blog.example.com

example.com/page 结果 #VALUE！

www.example.com/page results in #VALUE！

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!

正如你在上面的示例中可以看到的，我会得到很好的结果。但是它没有http或https的工作。此版本还保留子域名。

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.

推荐答案

在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))

....这在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(,))

这篇关于使用Excel将URL修改为ROOT / SUBDOMAIN的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！