我有一个包含 URL 列表的 Excel 列,我正尝试使用新架构更新现有 URL。有数千个 URL 需要更新,因此我正尝试创建一个可以帮我处理此问题的函数。
这是我的一些条目的示例。
/online-sales/new-jersey/online/top-selling-items-in-new-jersey.html
/retail-sales/new-york/online/top-selling-retail-shops-in-new-york.html
它们都遵循相同的 URL 架构。我希望最终结果能够删除 URL 的前半部分和in-state.html
末尾的部分。
我尝试使用这个函数=REPLACE(A1,1,79,"")
,它可以给我我期望的结果,但是每个单元格可能有不同的位置,所以我不知道如何使用创建这个函数并使其动态化(如果可能)。
这是我的预期结果:
/online/top-selling-items/
/online/top-selling-retail-shops/
答案1
如果您拥有具有TEXTJOIN
和FILTERXML
功能的 Windows Excel 2016+,您可以尝试:
="/" &LEFT(TEXTJOIN("/",TRUE,FILTERXML("<t><s>" & SUBSTITUTE(A1,"/","</s><s>")&"</s></t>","//s[position()=2]|//s[last()]")),FIND("-in-",TEXTJOIN("/",TRUE,FILTERXML("<t><s>" & SUBSTITUTE(A1,"/","</s><s>")&"</s></t>","//s[position()=2]|//s[last()]")))-1) & "/"
如果您没有这些功能,您可以使用以下命令:
- 定义命名公式:
Formula-->Define Name
name: seq
Refers to: =ROW(INDEX(Sheet1!$A:$A,1):INDEX(Sheet1!$A:$A,255))
然后使用这个公式:
=CONCATENATE("/", INDEX(TRIM(MID(SUBSTITUTE(A1,"/",REPT(" ",999)),IF(seq=1,1,(seq-1)*999),999)),2),"/",LEFT(INDEX(TRIM(MID(SUBSTITUTE(A1,"/",REPT(" ",999)),IF(seq=1,1,(seq-1)*999),999)),SUM(N(LEN(TRIM(MID(SUBSTITUTE(A1,"/",REPT(" ",999)),IF(seq=1,1,(seq-1)*999),999)))>0),1)),FIND("-in-",INDEX(TRIM(MID(SUBSTITUTE(A1,"/",REPT(" ",999)),IF(seq=1,1,(seq-1)*999),999)),SUM(N(LEN(TRIM(MID(SUBSTITUTE(A1,"/",REPT(" ",999)),IF(seq=1,1,(seq-1)*999),999)))>0),1)))-1),"/")
两种配方
- 将字符串拆分为由
/
- 返回与之连接的第二个和最后一个节点
/
- 注意由于字符串以分隔符开头,因此第一个节点将为空白。
- 使用
LEFT
和FIND
,仅返回最后一个节点之前的部分-in-
/
在字符串前面添加和后面添加 a 。
注意:如果在最后一个节点中可能存在多个-in-
,则公式需要修改
两者都返回相同的结果: