使用FILTERXML之后返回子字符串 [英] Returning substring-after using FILTERXML

问题描述

在一个现已删除的问题中，OP要求从 1 ^ 1.3％2 ^ 0.4 返回 1.3 和 0.4 .

In a now deleted question, the OP asked to return 1.3 and 0.4 from 1^1.3%2^0.4.

我立即想到了FILTERXML.

I immediately thought FILTERXML.

我能够做到:

=MID(FILTERXML("<a><b>"&SUBSTITUTE(A1,"%","</b><b>")&"</b></a>","//b"),FIND("^",FILTERXML("<a><b>"&SUBSTITUTE(A1,"%","</b><b>")&"</b></a>","//b"))+1,99) 

但是那似乎太重复了.

我在XPATH 1.0中找到了 substring-after ，但无法使其执行我想要的操作.我首先尝试过:

I found substring-after in XPATH 1.0, but cannot get it to do what I want. I first tried:

=FILTERXML("<a><b>"&SUBSTITUTE(A1,"%","</b><b>")&"</b></a>","//b[substring-after(., '^')]")

但是这返回了完整的字符串和:

But that returned both full strings and :

=FILTERXML("<a><b>"&SUBSTITUTE(A1,"%","</b><b>")&"</b></a>","substring-after(//b, '^')")

刚返回一个错误.

是否可以仅使用FILTERXML返回所需的数字?

Is there a way to use only FILTERXML to return the desired numbers?

推荐答案

不幸的是，Excel中的 XPATH 似乎无法返回重做的值，而是使用 FILTERXML 进行过滤并返回这些值感兴趣的节点.返工这些节点需要在功能之外完成.

Unfortunately XPATH in Excel cannot seem to return reworked values, instead use FILTERXML for filtering and returning those nodes of interest. Reworking those nodes need to be done outside the function.

如果您使用单一的 FILTERXML 进行测试，我建议您使用两个 SUBSTITUTE :

If you are intested in a single FILTERXML usage, I'd suggest a double SUBSTITUTE:

=FILTERXML("<a><b>"&SUBSTITUTE(SUBSTITUTE(A1,"^","^%"),"%","</b><b>")&"</b></a>","//b[contains(preceding::*[1],'^')]")

或者:

=FILTERXML("<a><b>"&SUBSTITUTE(SUBSTITUTE(A1,"^","^%"),"%","</b><b>")&"</b></a>","//b[.*0=0]")

您可能对通过 FILTERXML 进行过滤感兴趣.也许这会让您感兴趣吗?

Might you be interested in filtering through FILTERXML. Maybe this would interest you?

这篇关于使用FILTERXML之后返回子字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！