Excel - 使用FILTERXML从字符串中提取子字符串

Question

Excel - 使用FILTERXML从字符串中提取子字符串

37

_背景

最近我一直在试图了解将分隔字符串转换为XML以便用Excel的FILTERXML解析，并检索那些感兴趣的子字符串。请注意，此函数可从Excel 2013中使用，但在Excel for Mac和Excel Online上不可用。

对于分隔的字符串，我指的是使用空格作为分隔符或任何其他字符组合来定义字符串内子字符串的普通句子等。例如，让我们想象以下内容：

ABC|123|DEF|456|XY-1A|ZY-2F|XY-3F|XY-4f|xyz|123

_问题

许多人都知道如何获取第 n 个元素（例如：=TRIM(MID(SUBSTITUTE(A1,"|",REPT(" ",LEN(A1))),3*LEN(A1)+1,LEN(A1))) 返回 456），或者通过使用 LEN()、MID()、FIND() 和所有这些构造的其他组合，来如何使用 FILTERXML 根据更具体的条件提取感兴趣的子字符串并清理完整字符串？例如，如何检索：

按位置的元素
数字或非数字元素
包含特定子字符串的元素
以特定子字符串开头或结尾的元素
大写或小写的元素
包含数字的元素
唯一值
...

- JvdV

2个回答

16

2022年3月22日编辑:

根据新的TEXTSPLIT()函数，现在可以将字符串直接拆分成水平或垂直范围（或两者兼备）。它甚至可以在值数组上进行拆分。但是，它不能像“xpath”一样直接过滤。因此，我将保持下面的答案不变，因为它仍然可能相关。

臭名昭著的缺失`XSPLIT()`函数

本文旨在深入介绍如何使用FILTERXML()创建可重复使用的XSPLIT()函数，但不需要使用VBA。虽然目前处于BETA版本，但LAMBDA()即将推出，有了这个函数，我们就可以创建自己的自定义函数。让我通过一个例子来解释：

C1 中的公式是简单的 =XSPLIT(A1,B1:B3)，它会按照出现顺序填充限定符文本值。然而，SPLIT() 是我们在“名称管理器”中创建的 LAMBDA() 函数的名称：

=LAMBDA(txt,del,[xpath],FILTERXML("<t><s>"&TEXTJOIN("</s><s>",,TEXTSPLIT(txt,del))&"</s></t>","//s"&xpath))

正如您所看到的，该函数有4个参数：

txt - 源值的引用。
del - 任意数量的分隔符，可以是文字或引用。
[xpath] - 一个xpath表达式的位置，如果需要，可以应用一些过滤器。例如："[.*0=0]" 只返回数字子字符串。这是一个可选参数。
FILTERXML("<t><s>"&TEXTJOIN("</s><s>",,TEXTSPLIT(txt,del))&"</s></t>","//s"&xpath)

第四个参数是将前三个参数调用以创建与主帖中涵盖的相同结构的地方。由于微软不想给我们提供，因此我们使用了自己的XSPLIT()函数来处理这三个参数。

主要帖子集中在特定分隔符的SUBSTITUTE()上；在给定的示例中是管道符号。但是如果您有多个分隔符呢？您需要多个嵌套的SUBSTITUTE()函数，对吧？如果我们也可以在XSPLIT()函数中实现这一点，那不是很好吗？这就是为什么TEXTSPLIT()对我个人来说变得令人兴奋，因为我们可以将多个分隔符馈送到此函数中以将字符串拆分为元素。

我们现在使用三个参数创建了自己的XSPLIT()函数：

=XSPLIT(<StringToBeSplited>,<YourDelimiters>,<OptionalXpath>)

现在我们可以将其作为函数在整个工作簿中使用。享受吧！

- JvdV

1

用出色的答案让南非同胞感到自豪 - 干得好兄弟（假设我没有搞错性别 :)）。我只是涉猎了Lambda，还需要理解reduce函数 - 对于Excel知识似乎没有巅峰。无论如何 - 我会给你点赞，继续加油。 - JB-007

1

嗨，@JB-007。谢谢你的赞美。继续学习，如果需要帮助，请不要犹豫问我。 - JvdV

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- JvdV · Accepted Answer

Excel的FILTERXML使用XPATH 1.0，这意味着它的多样性不如我们所希望的那么丰富。此外，Excel似乎不允许返回重新处理的节点值，并且仅允许按照出现顺序选择节点。但是，我们仍然可以利用相当数量的函数。更多信息可以在此处找到：这里。

该函数需要两个参数：=FILTERXML(＜一个有效的XML格式字符串＞,＜一个有效的XPATH格式字符串＞)

假设单元格A1包含字符串：ABC|123|DEF|456|XY-1A|ZY-2F|XY-3F|XY-4f|xyz|123。为了创建一个有效的XML字符串，我们使用SUBSTITUTE将分隔符更改为有效的结束和开始标记结构。因此，为了针对给定示例获得有效的XML结构，我们可以执行以下操作：

"<t><s>"&SUBSTITUTE(A1,"|","</s><s>")&"</s></t>"

出于可读性的原因，我将使用单词<XML>作为占位符来引用上述结构。以下是在有效结构中过滤节点的不同有用的XPATH函数：

_{1) 所有元素：}

=FILTERXML(<XML>,"//s")

_{返回：ABC、123、DEF、456、XY-1A、ZY-2F、XY-3F、XY-4f、xyz和123（所有节点）}

_{2) 位置选择元素：}

=FILTERXML(<XML>,"//s[position()=4]")

或：

=FILTERXML(<XML>,"//s[4]")

返回：< code >456< /code >（第4个节点）†

=FILTERXML(<XML>,"//s[position()<4]")

_{返回：ABC、123 和 DEF（索引小于 4 的节点）}

=FILTERXML(<XML>,"//s[position()=2 or position()>5]")

_{返回: 123, ZY-2F, XY-3F, XY-4f, xyz和123（在索引2或大于5的节点）}

=FILTERXML(<XML>,"//s[last()]")

_{返回：123（最后一个索引上的节点）}

=FILTERXML(<XML>,"//s[position() mod 2 = 1]")

_{返回：ABC、DEF、XY-1A、XY-3F 和 xyz（奇数节点）}

=FILTERXML(<XML>,"//s[position() mod 2 = 0]")

_{返回：123、456、ZF-2F、XY-4f和123（偶数节点）}

_{3)（非）数字元素：}

=FILTERXML(<XML>,"//s[number()=.]")

或者：

=FILTERXML(<XML>,"//s[.*0=0]")

_{返回：123、456和123（数字节点）}

=FILTERXML(<XML>,"//s[not(number()=.)]")

或者:

=FILTERXML(<XML>,"//s[.*0!=0)]")

_{返回：ABC、DEF、XY-1A、ZY-2F、XY-3F、XY-4f 和 xyz（非数字节点）}

_{4) 包含（不包含）元素：}

=FILTERXML(<XML>,"//s[contains(., 'Y')]")

_{返回：XY-1A、ZY-2F、XY-3F 和 XY-4f（包含“Y”，注意 XPATH 区分大小写，排除 xyz）。}

=FILTERXML(<XML>,"//s[not(contains(., 'Y'))]")

_{返回：ABC、123、DEF、456、xyz和123（不包括‘Y’，注意XPATH区分大小写，包括xyz）}

_{5) 以（不以）某些元素开头或/和结尾：}

=FILTERXML(<XML>,"//s[starts-with(., 'XY')]")

_{返回：XY-1A、XY-3F和XY-4f（以“XY”开头）}

=FILTERXML(<XML>,"//s[not(starts-with(., 'XY'))]")

_{返回：ABC、123、DEF、456、ZY-2F、xyz 和 123（不以“XY”开头）}

=FILTERXML(<XML>,"//s[substring(., string-length(.) - string-length('F') +1) = 'F']")

_{返回：DEF、ZY-2F 和 XY-3F（以“F”结尾，注意XPATH 1.0不支持ends-with）}

=FILTERXML(<XML>,"//s[not(substring(., string-length(.) - string-length('F') +1) = 'F')]")

_{返回：ABC，123，456，XY-1A，XY-4f，xyz和123（不以“F”结尾）}

=FILTERXML(<XML>,"//s[starts-with(., 'X') and substring(., string-length(.) - string-length('A') +1) = 'A']")

_{返回：XY-1A（以“X”开头，以“A”结尾）}

_{6）大写或小写的元素：}

=FILTERXML(<XML>,"//s[translate(.,'abcdefghijklmnopqrstuvwxyz','ABCDEFGHIJKLMNOPQRSTUVWXYZ')=.]")

_{返回：ABC，123，DEF，456，XY-1A，ZY-2F，XY-3F和123（大写节点）}

=FILTERXML(<XML>,"//s[translate(.,'ABCDEFGHIJKLMNOPQRSTUVWXYZ','abcdefghijklmnopqrstuvwxyz')=.]")

_{返回值: 123, 456, xyz 和 123（小写节点）}

_{注意: 不幸的是，XPATH 1.0 不支持 upper-case() 和 lower-case()，所以上述内容是一个解决方法。如有需要，请添加特殊字符。}

_{7) 包含或不包含任何数字的元素：}

=FILTERXML(<XML>,"//s[translate(.,'1234567890','')!=.]")

_{返回：123、456、XY-1A、ZY-2F、XY-3F、XY-4f和包含任何数字的123}

=FILTERXML(<XML>,"//s[translate(.,'1234567890','')=.]")

_{返回：ABC，DEF和xyz（不包含任何数字）}

=FILTERXML(<XML>,"//s[translate(.,'1234567890','')!=. and .*0!=0]")

_{返回：XY-1A、ZY-2F、XY-3F 和 XY-4f（保留数字但不作为单独的数字存在）}

_{8）唯一元素或重复元素：}

=FILTERXML(<XML>,"//s[preceding::*=.]")

_{返回：123（重复节点）}

=FILTERXML(<XML>,"//s[not(preceding::*=.)]")

_{返回：ABC、123、DEF、456、XY-1A、ZY-2F、XY-3F、XY-4f和xyz（唯一节点）}

=FILTERXML(<XML>,"//s[not(following::*=. or preceding::*=.)]")

_{返回：ABC、DEF、456、XY-1A、ZY-2F、XY-3F和XY-4f（没有类似兄弟节点的节点）}

_{9) 特定长度的元素：}

=FILTERXML(<XML>,"//s[string-length()=5]")

_{返回：XY-1A，ZY-2F，XY-3F和XY-4f（长度为5个字符）}

=FILTERXML(<XML>,"//s[string-length()<4]")

_{返回：ABC，123，DEF，456，xyz和短于4个字符的123}

_{10) 基于前面/后面的元素：}

=FILTERXML(<XML>,"//s[preceding::*[1]='456']")

_{返回: XY-1A（上一个节点为'456'）}

=FILTERXML(<XML>,"//s[starts-with(preceding::*[1],'XY')]")

_{返回：ZY-2F、XY-4f和xyz（前一个节点以“XY”开头）}

=FILTERXML(<XML>,"//s[following::*[1]='123']")

_{返回：ABC和xyz（后续节点等于'123'）}

=FILTERXML(<XML>,"//s[contains(following::*[1],'1')]")

_{返回：ABC，456和xyz（以下节点包含“1”）}

=FILTERXML(<XML>,"//s[preceding::*='ABC' and following::*='XY-3F']")

或者：

=FILTERXML(<XML>,"//s[.='ABC']/following::s[following::s='XY-3F']")

_{返回：123，DEF，456，XY-1A和ZY-2F（在“ABC”和“XY-3F”之间的所有内容）}

_{11）基于子字符串的元素：}

=FILTERXML(<XML>,"//s[substring-after(., '-') = '3F']")

_{返回：XY-3F（连字符后面以“3F”结尾的节点）}

=FILTERXML(<XML>,"//s[contains(substring-after(., '-') , 'F')]")

_{返回：ZY-2F和XY-3F（包含连字符后面有 'F' 的节点）}

=FILTERXML(<XML>,"//s[substring-before(., '-') = 'ZY']")

_{返回：ZY-2F（连字符前以“ZY”开头的节点）}

=FILTERXML(<XML>,"//s[contains(substring-before(., '-'), 'Y')]")

_{返回：XY-1A、ZY-2F、XY-3F和XY-4f（节点包含连字符前面的“Y”）}

_{12) 基于连接的元素：}

=FILTERXML(<XML>,"//s[concat(., '|', following::*[1])='ZY-2F|XY-3F']")

_{返回：ZY-2F（与 '|' 连接并且其后续同级节点为 'ZY-2F|XY-3F' 时的节点）}

=FILTERXML(<XML>,"//s[contains(concat(., preceding::*[2]), 'FA')]")

_{返回：DEF（与左侧两个同级节点连接时包含“FA”的节点）}

_{13）空 vs. 非空：}

=FILTERXML(<XML>,"//s[count(node())>0]")

或：

=FILTERXML(<XML>,"//s[node()]")

_{返回：ABC、123、DEF、456、XY-1A、ZY-2F、XY-3F、XY-4f、xyz 和 123（所有非空节点）}

=FILTERXML(<XML>,"//s[count(node())=0]")

或：

=FILTERXML(<XML>,"//s[not(node())]")

_{返回值：None（所有为空的节点）}

_{14）前驱或后继：}

=FILTERXML(<XML>,"//s[substring(., string-length(.) - string-length('F') +1) = 'F'][last()]/following::*")

_{返回：XY-4f、xyz和123（所有以大写字母'F'结尾的最后一个节点右侧的所有节点）}

=FILTERXML(<XML>,"//s[substring(., string-length(.) - string-length('F') +1) = 'F'][1]/preceding::*")

_{返回：ABC和123（所有节点位于以大写字母'F'结尾的第一个节点左侧）}

_{15)（前置或后置）和self：}

=FILTERXML(<XML>,"(//s[.*0!=0][last()]|//s[.*0!=0][last()]/preceding::*)")

_{返回：ABC，123，DEF，456，XY-1A，ZY-2F，XY-3F，XY-4f和xyz（从右侧删除所有数字节点）}^††

=FILTERXML(<XML>,"(//s[.*0=0][1]|//s[.*0=0][1]/following::*)")

Returns: 123, DEF, 456, XY-1A, ZY-2F, XY-3F, XY-4f, xyz and 123 (trim all non-numeric nodes from the left)。

_{16) 最大值或最小值：}

=FILTERXML(<XML>,"(//s[.*0=0][not(.<//s[.*0=0])])[1]")

_{返回：456（查看数字节点时的最大值）}

=FILTERXML(<XML>,"(//s[.*0=0][not(.>//s[.*0=0])])[1]")

_{返回：123（查看数值节点的最小值）}

_{注意：这相当于根据＃3返回所有数值节点，然后使用Excel的MIN()和MAX()函数对数组进行后处理。}

显然，以上是展示XPATH 1.0 函数可能性的一个演示，您可以获得各种组合及更多内容！我试图涵盖大多数常用的字符串函数。如果您缺少任何内容，请随时评论。

虽然问题本身很广泛，但我希望提供一些有关如何使用FILTERXML查询的一般方向。该公式返回要在其他任何方式中使用的节点数组。很多时候我会在TEXTJOIN（）或INDEX（）中使用它。但是我想其他选项将是新的DA函数来溢出结果。

请注意，在通过FILTERXML（）解析字符串时，和号字符（&）和左角括号（<）必须不以其文字形式出现。不。它们分别需要用&或<替换。另一个选项是使用它们的数字ISO/IEC 10646字符代码分别为&或<。解析后，该函数将以其文字形式返回这些字符。不用说，通过半冒号拆分字符串因此变得棘手。

^‡_{每个谓词，即打开和关闭方括号之间的结构，都是给定节点列表的过滤器。编写多个这些结构实际上是anding这样的谓词。}

^‡‡_{没有真正的易于遵循/前面的兄弟和自我结构。因此，我使用了联合运算符。但是，这需要将多个表达式放在括号内。如果考虑正则表达式中的捕获组内的交替，则与之类似。}

Excel - 使用FILTERXML从字符串中提取子字符串

臭名昭著的缺失XSPLIT()函数

臭名昭著的缺失`XSPLIT()`函数