使用 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屋!