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

2024-05-04

_Background

最近，我一直在尝试更熟悉将分隔字符串更改为 XML 以使用 Excel 进行解析的概念FILTERXML https://support.microsoft.com/en-us/office/filterxml-function-4df72efc-11ec-4951-86f5-c1374812f5b7并检索那些感兴趣的子字符串。请注意，此功能从 Excel 2013 开始提供，在 Excel for Mac 或 Excel Online 上不可用。

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

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

_Question

所以，很多人都知道如何获得nth元素（例如：=TRIM(MID(SUBSTITUTE(A1,"|",REPT(" ",LEN(A1))),3*LEN(A1)+1,LEN(A1)))检索456）。或者其他组合LEN(), MID(), FIND()以及所有这些构造，我们如何使用FILTERXML使用更具体的标准来提取关注的子字符串并清理整个字符串？例如如何检索：

按位置排列的元素
数字或非数字元素
自己包含子字符串的元素
以子字符串开头或结尾的元素
大写或小写的元素
持有数字的元素
独特的价值观
...

Excel's FILTERXML uses XPATH 1.0不幸的是，这意味着它并不像我们希望的那样多样化。另外，Excel 似乎not允许返回修改后的节点值，并且专门允许您按出现顺序选择节点。然而，我们仍然可以使用相当一部分功能。可以找到更多相关信息here https://www.w3.org/TR/1999/REC-xpath-19991116/.

该函数有两个参数：=FILTERXML(<A string in valid XML format>,<A string in valid XPATH format>)

比如说细胞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> as a 占位符。下面你会发现不同的有用XPATH有效构造中的函数用于过滤节点：

_{1) All Elements:}

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

_{Returns: ABC, 123, DEF, 456, XY-1A, ZY-2F, XY-3F, XY-4f, xyz and 123 (all nodes)}

_{2) Elements by position:}

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

Or:

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

_{Returns: 456 (node on index 4)}^†

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

_{Returns: ABC, 123 and DEF (nodes on index < 4)}

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

_{Returns: 123, ZY-2F, XY-3F, XY-4f, xyz and 123 (nodes on index 2 or > 5)}

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

_{Returns: 123 (node on last index)}

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

_{Returns: ABC, DEF, XY-1A, XY-3F and xyz (odd nodes)}

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

_{Returns: 123, 456, ZF-2F, XY-4f and 123 (even nodes)}

_{3) (Non) numeric elements:}

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

Or:

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

_{Returns: 123, 456, and 123 (numeric nodes)}

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

Or:

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

_{Returns: ABC, DEF, XY-1A, ZY-2F, XY-3F, XY-4f and xyz (non-numeric nodes)}

_{4) Elements that (not) contain:}

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

_{Returns: XY-1A, ZY-2F, XY-3F and XY-4f (containing 'Y', notice XPATH is case sensitive, exclusing xyz)}

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

_{Returns: ABC, 123, DEF, 456, xyz and 123 (not containing 'Y', notice XPATH is case sensitive, including xyz)}

_{5) Elements that (not) start or/and end with:}

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

_{Returns: XY-1A, XY-3F and XY-4f (starting with 'XY')}

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

_{Returns: ABC, 123, DEF, 456, ZY-2F, xyz and 123 (don't start with 'XY')}

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

_{Returns: DEF, ZY-2F and XY-3F (end with 'F', notice XPATH 1.0 does not support ends-with)}

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

_{Returns: ABC, 123, 456, XY-1A, XY-4f, xyz and 123 (don't end with 'F')}

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

_{Returns: XY-1A (start with 'X' and end with 'A')}

_{6) Elements that are upper- or lowercase:}

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

_{Returns: ABC, 123, DEF, 456, XY-1A, ZY-2F, XY-3F and 123 (uppercase nodes)}

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

_{Returns: 123, 456, xyz and 123 (lowercase nodes)}

_{NOTE: Unfortunately XPATH 1.0 does not support upper-case() nor lower-case() so the above is a workaround. Add special characters if need be.}

_{7) Elements that (not) contain any number:}

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

_{Returns: 123, 456, XY-1A, ZY-2F, XY-3F, XY-4f and 123 (contain any digit)}

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

_{Returns: ABC, DEF and xyz (don't contain any digit)}

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

_{Returns: XY-1A, ZY-2F, XY-3F and XY-4f (holding digits but not a a number on it's own)}

_{8) Unique elements or duplicates:}

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

_{Returns: 123 (duplicate nodes)}

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

_{Returns: ABC, 123, DEF, 456, XY-1A, ZY-2F, XY-3F, XY-4f and xyz (unique nodes)}

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

_{Returns: ABC, DEF, 456, XY-1A, ZY-2F, XY-3F and XY-4f (nodes that have no similar sibling)}

_{9) Elements of certain length:}

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

_{Returns: XY-1A, ZY-2F, XY-3F and XY-4f (5 characters long)}

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

_{Returns: ABC, 123, DEF, 456, xyz and 123 (shorter than 4 characters)}

_{10) Elements based on preceding/following:}

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

_{Returns: XY-1A (previous node equals '456')}

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

_{Returns: ZY-2F, XY-4f, and xyz (previous node starts with 'XY')}

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

_{Returns: ABC, and xyz (following node equals '123')}

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

_{Returns: ABC, 456, and xyz (following node contains '1')}

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

Or:

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

_{Returns: 123, DEF, 456, XY-1A and ZY-2F (everything between 'ABC' and 'XY-3F')}

_{11) Elements based on sub-strings:}

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

_{Returns: XY-3F (nodes ending with '3F' after hyphen)}

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

_{Returns: ZY-2F and XY-3F (nodes containing 'F' after hyphen)}

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

_{Returns: ZY-2F (nodes starting with 'ZY' before hyphen)}

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

_{Returns: XY-1A, ZY-2F, XY-3F and XY-4f (nodes containing 'Y' before hyphen)}

_{12) Elements based on concatenation:}

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

_{Returns: ZY-2F (nodes when concatenated with '|' and following sibling equals 'ZY-2F|XY-3F')}

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

_{Returns: DEF (nodes when concatenated with sibling two indices to the left contains 'FA')}

_{13) Empty vs. Non-empty:}

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

Or:

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

_{Returns: ABC, 123, DEF, 456, XY-1A, ZY-2F, XY-3F, XY-4f, xyz and 123 (all nodes that are not empty)}

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

Or:

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

_{Returns: None (all nodes that are empty)}

_{14) Preceding or Following:}

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

_{Returns: XY-4f, xyz and 123 (all nodes to the right of the last node that ends with an uppercase 'F')}

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

_{Returns: ABC and 123 (all nodes to the left of the first node that ends with an uppercase 'F')}

_{15) (Preceding or Following) and self:}

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

_{Returns: ABC, 123, DEF, 456, XY-1A, ZY-2F, XY-3F, XY-4f and xyz (trim all numeric nodes from the right)}^††

=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) Maximum or Minimum:}

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

_{Returns: 456 (The maximum value looking at numeric nodes)}

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

_{Returns: 123 (The minimum value looking at numeric nodes)}

_{NOTE: This is the equivalent to returning all numeric nodes as per #3 and post-process the array using Excel's MIN() and MAX() functions.}

现在显然上面是可能性的演示XPATH 1.0功能，您可以获得上述及更多功能的一系列组合！我试图涵盖最常用的字符串函数。如果您有遗漏，请随时评论。

尽管问题本身相当广泛，但我希望就如何使用提供一些总体指导FILTERXML对于手头的查询。该公式返回要以任何其他方式使用的节点数组。很多时候我会用它TEXTJOIN() or INDEX()。但我想其他选项将是新的 DA 功能来泄露结果。

请注意，在解析字符串时FILTERXML()、与号字符 (&) 和左尖括号 (not以字面形式出现。它们分别需要替换为& or <。另一种选择是使用 ISO/IEC 10646 数字字符code https://m.tonysweb.be/ISO_IEC%2010646-12000(E).html being & or <分别。解析后，该函数将以文字形式将这些字符返回给您。不用说，用分号分割字符串就变得很棘手。

^‡_{Each predicate, the structure between the opening and closing square brackets, is a filter of a given nodelist. To write multiple of these structures is in fact anding https://stackoverflow.com/questions/62969654/xpath-what-do-nested-square-brackets-mean/62970207#62970207 such predicates.}

^‡‡_{There isn't really an easy, following/preceding siblings and self, construct. Therefor I used the Union https://examples.javacodegeeks.com/core-java/xml/xpath/xpath-union-operator-example/ operator. This, however, requires multiple expressions to be inside paranthesis https://stackoverflow.com/a/26598269/9758194. Much like alternations within a capture group if one would think about regular expressions.}

本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)

Excel - 使用 FILTERXML 从字符串中提取子字符串的相关文章

Python：结构体和数组与 ctypes 中的类似功能

Python 提供了以下三个处理 C 类型以及如何处理它们的模块 struct https docs python org 3 library struct html对于 C 结构体 array https docs python org
给定一个具有多个重复条目的数组，找到一个重复条目 O(N) 时间和常数空间

我们得到了一个大小为 N 的数组其中包含 0 到 N 2 范围内的整数包括 0 和 N 2 该数组可以有多个重复的条目我们需要在 O N 时间和常量空间中找到重复条目之一我正在考虑取数组中所有条目的乘积和总和以及 0 到 N 2
如何初始化一个最初大小未知的数组？

假设我有这个 int x int x State Determined By Program const char pArray const int x 在使用 pArray 之前如何初始化它因为Array的初始大小是由用户输入决定的 T
文件夹.文件的相对路径

我有一个 Excel 文件在同一文件夹中还有一个包含我想要包含的 CSV 文件的文件夹使用来自文件夹查询第一步将给出以下查询 Folder Files D OneDrive Documents Health Concept2 现在
定义 js-xlsx 单元格范围

我正在尝试使用 js xlsx 读取 Excel 值我可以使用以下代码从工作簿工作表中获取单元格值 if typeof require undefined XLSX require xlsx var workbook XLSX readF
在 VBA Excel 中查找、剪切和插入行以匹配借项和贷项值

我在 Sheet1 中有以下设置数据并从第 4 行 A 列开始其中标题位于第 3 行 No Date Code Name Remarks D e b i t Cr e d i t 1 4 30 2015 004 AB 01 04 15
删除Android所有语言中的字符串

我有一个包含多个翻译的应用程序我想删除一些字符串我怎样才能重构并删除它们一次例如在默认情况下strings xml文件并自动将删除传播到其他翻译的其他 strings xml 文件您可以通过 Android Studio 中的翻译
如何在 Spring 属性中进行算术运算？
JavaScript 数组扩展语法的时间复杂度是多少？

我想知道在 JavaScript 中使用数组扩展的时间复杂度是多少是线性 O n 还是常数 O 1 下面的语法示例 let lar Math max nums 传播称为 Symbol iterator 有关对象的属性对于数组这将迭代数
泛型、数组和 ClassCastException

我想这里一定发生了一些我不知道的微妙事情考虑以下 public class Foo
Android：默认 XML 编辑器不再打开 [不支持的内容类型错误]

我使用 Eclipse 在 Android 中开发应用程序已经有一段时间了我最近将Android SDK平台更新到3 0 API 11 现在我无法通过双击打开 AndroidManifest xml 或 Layout 文件夹中的任何其他
使用 XML 和 C# 创建 Word 文档

我已经为报告创建了一个 xml 模板用户应该能够通过我用 C 创建的程序添加信息以根据自己的需要个性化此报告如何根据用户输入文本框的内容编辑实体的内容然后将其显示在 Word 上网上有大量有关使用 XML 和 C 创建 Word 文
使用字符串中的变量名称访问变量值，R

Intro 一个数据集有大量的age year变量 age 1990 age 1991 etc 我有一个字符串值数组length age years 表示这些变量使得age years 1 回报 age 1990 etc Need 我想搜
在二维范围内查找匹配的公式

我需要一个公式来查找二维范围内的值并返回匹配单元格的坐标或单元格地址例如 R A B C 1 John Matt Pete 2 Sara Bret Chad 3 Lila Maya Cami 我想搜索范围A1 C3 for Chad并返回
重新排列数组键 php [重复]

这个问题在这里已经有答案了我有这个数组 Array 15 gt 13 1 16 gt Mark one answer 19 gt You see a car on the hard shoulder of a motorway with
将 Xml 反序列化为对象时出错 - xmlns='' 不是预期的

我在尝试反序列化某些 XML 时遇到了真正的麻烦希望有人可以提供一些帮助我读过很多类似的帖子但我无法解决这个问题我正在尝试反序列化 XML
使用 sed 更新 xml 属性（Windows + cygwin 和 Linux）？

我需要使用 sed 命令对 xml 文件进行更新但我在这方面遇到了麻烦它需要在 Windows 使用 cygwin 和 Linux 上运行 XML 具有以下元素
Minizinc：生成有效的转变

希望有人能帮助我解决这个问题最初的问题是生成有效的班次如下所述我有这样的数组 m m m o o l l m m m l m m m 具有固定长度 S 其中 m 是工作 o 是办公室我自由了我需要确保至少每 6m 就有两个 l 在
Java-如何将黑白图像加载到二进制中？

我在 FSE 模式下使用 Java 和 swing 我想将完全黑白图像加载为二进制格式最好是二维数组并将其用于基于掩码的每像素碰撞检测我什至不知道从哪里开始过去一个小时我一直在研究但没有找到任何相关的东西只需将其读入Buffer
如何使用C#在asp.net中下载xml文件

我正在使用网络应用程序ASP NET 与 MVC3 我是 mvc3 的新手我的网页上有一个下载按钮当我要单击下载按钮时我希望能够打开该 XML 文件我尝试过一些代码更改动作结果但我没有打开文件通过使用下面提到的代码我收到一个下载

随机推荐

C# .NET 串口已连接，但无法读取或写入

我使用 C 和 NET 4 5 以及 Visual Studio 2012 编译器 IDE 来打开串行端口并与之交互我的代码旨在连接到 US Digital 的 QSB 正交 USB 转换器这是我用来打开端口并连接的代码 this Po
使用 AVFoundation 快速获取视频帧

这是我的代码我想要获取我的视频并将帧数据获取到 SceneKit SCNSphere NSString videoPath NSBundle mainBundle l var videoURL NSBundle mainBundle UR
如何将 YouTube API 集成到我的 iPhone 应用程序中？

我想将 YouTube API 集成到我的应用程序中我该怎么做附注我正在为 YouTube 频道制作一个应用程序我尝试以webview 但这让一切变得更糟因为用户可以看到 YouTube 控件搜索等以及有关 YouTube i
从 Django Rest Framework 中的令牌获取经过身份验证的用户

我是 Django 新手我已经成功使用 DRF 构建了一个小型 API 我的 angular js 客户端发布了用户身份验证详细信息 DRF 返回一个令牌如下所示 token 9944b09199c62bcf9418ad846dd0e4
如何实现google paper按钮效果

谷歌的纸张材料设计http www google com design spec material design introduction html http www google com design spec material des
while循环有两个参数吗？

我的女士给了我一个问题要我解答预测以下代码的输出 include
Python/Numpy - 屏蔽数组非常慢

我可以做些什么来加速 numpy 中的屏蔽数组吗我有一个效率非常低的函数我重新编写了它来使用屏蔽数组我可以只屏蔽行而不是像我所做的那样进行复制和删除行然而我惊讶地发现 masked 函数慢了 10 倍因为 masked 数组慢
keep-order 设置 true 不会按照定义的组依赖关系的顺序运行 TestNG 测试

我正在尝试运行分组 TestNG 测试给定的 xml 是 testng xml 中的测试
ExtJs 4，从容器中删除控件并将其从内存中删除的正确方法是什么？

这个问题很简单但我找不到一个好的明确的答案正确的方法是什么从容器中删除控件在 ExtJs 4 中将其从内存中删除 Ext AbstractContainer remove http dev sencha com deploy ex
git merge，保留两者

为了合并我用它来保留我的 git merge X ours foo 这是保留他们的 git merge X theirs foo 然而在我最近的合并中看起来最好保留双方 Git 是否有一个策略来避免手动编辑文件没有解决这些冲
jQuery 添加和删除类

我有这个选择器
双击选项卡控制标题

一段时间以来我一直想知道这个问题双击 winforms TabControl 的标题没有被检测到但我想处理这个事件看起来所有鼠标事件单击移动等在 TabControl 的非活动区域上时都不会引发我什至尝试过对 TabCon
从文本文件中读取丹麦语字符

我正在尝试读取包含一些丹麦语字符的文本文件我找到了几种使用不同类型的编码来完成此操作的方法但我看到的示例仅读取一个文件这是我到目前为止所拥有的 search directory for all txt files foreach st
如何更改 max_allowed_packet 大小

我的 MySQL 数据库中的 BLOB 字段出现问题上传大于约 1MB 的文件时出现错误Packets larger than max allowed packet are not allowed 这是我尝试过的在 MySQL 查询浏览
永远不应该触发嵌套优化。这可能是由于 NSISVariable 委托回调内部发生自动布局工作

应用程序崩溃了日志给了我这条消息永远不应该触发嵌套优化这可能是由于自动布局工作发生在 NSISVariable 委托回调内这是不允许的如何解决这个问题认为我正在后台线程中更新 UI 尝试放置 if NSThread isMain
带有自定义按钮的 ExtJs 消息框

如何使用自定义按钮显示 ExtJS 消息框我想要一个带有自定义消息以及取消和停用按钮的消息框请给一些想法 buttons text Cancel handler function Ext MessageBox hide subm
创建应用程序：无法初始化 ORM

当我启动节点时我总是收到此错误请回复我我哪里出错了错误创建应用程序无法初始化 ORM initializeORM NewORM 无法初始化 DB 无法打开 application name Chainlink 0 10 7 7C
比较 Swift 中的 AnyObjects，无需将它们转换为特定类型

尝试使用 Equatable 协议中定义的运算符来比较 AnyObject 类型的两个对象会导致 Swift 中出现编译错误有没有人找到一种方法来比较这些对象而不知道可用于向下转换的对象的真实类型这个问题的背景是我有一个字典 Dic
Cordova 插件不适用于 Ionic

我正在 Angular 中构建一个 Ionic 应用程序但一直无法让插件工作例如我尝试使用状态栏插件如下所述 http ionicframework com tutorials fullscreen apps http ionicf
Excel - 使用 FILTERXML 从字符串中提取子字符串

Background 最近我一直在尝试更熟悉将分隔字符串更改为 XML 以使用 Excel 进行解析的概念FILTERXML https support microsoft com en us office filterxml funct

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

Excel - 使用 FILTERXML 从字符串中提取子字符串 的相关文章

随机推荐

热门标签

Excel - 使用 FILTERXML 从字符串中提取子字符串的相关文章