Google Sheets中如何从合并地址提取指定街道名称？

以下是针对你的场景的几个实用公式，适配不同Excel版本：

方案1：适配Excel 365/2021（动态数组支持） #

使用XLOOKUP直接匹配提取：

=XLOOKUP(TRUE,ISNUMBER(SEARCH(Sheet2!$A$1:$A,B2)),Sheet2!$A$1:$A,"")

说明：遍历Sheet2的街道名称列表，检测是否在当前地址（B2）中存在，返回第一个匹配的街道名称；无匹配时返回空值。

方案2：利用正则提取（支持REGEX函数的版本） #

复用你之前的正则匹配逻辑，直接提取：

=REGEXEXTRACT(B2, TEXTJOIN("|",TRUE,Sheet2!$A$1:$A))

说明：通过TEXTJOIN生成街道名称的正则匹配串，从B2的地址文本中提取第一个匹配到的街道名称。

方案3：兼容旧版Excel（无动态数组支持） #

使用INDEX+MATCH数组公式：

=INDEX(Sheet2!$A$1:$A,MATCH(TRUE,ISNUMBER(SEARCH(Sheet2!$A$1:$A,B2)),0))

说明：旧版Excel需按Ctrl+Shift+Enter作为数组公式输入，新版直接回车即可。通过SEARCH判断街道名称是否存在于地址中，找到第一个匹配项的位置后，用INDEX返回对应街道名。

额外注意事项 #

• 如果Sheet2的街道名称包含正则特殊字符（如.、*、?），需要先转义，避免匹配出错。例如将TEXTJOIN部分修改为：

  TEXTJOIN("|",TRUE,SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Sheet2!$A$1:$A,".","\."),"*","\*"),"?","\?"))
  

• 确保Sheet2的街道名称列表无重复值，否则公式会返回第一个匹配的结果。

内容的提问来源于stack exchange，提问作者Rick Spence