使用 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("/";SUBSTITUTE(A2;"www.";"");9)-SEARCH(":";A2)-3)

https://example.com/page/page 结果在 example.com
http://www.example.com/page/page 结果在 example.com
http://blog.example.com/page/ 结果在 blog.example.com
example.com/page 结果为 #VALUE!
www.example.com/page 结果为 #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 中试试这个，

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

.... 而这在 C1 中，

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

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