☰

Excel 2010 - 从单元格中删除字符，字符数有所不同

tag-icon • tag-icon microsoft-excel worksheet-function microsoft-excel-2010
Excel 2010 - 从单元格中删除字符，字符数有所不同

我有一个从其他应用程序导出的列表。返回的结果中间带有多余的字符。

我得到的是：

Mary One;#123;#Bob Two;#2345;#Charles Three;#445

我想要的是：

Mary One; Bob Two; Charles Three

单元格中可以列出的用户数量并不一致。更复杂的因素是数字在 3 到 4 个字符之间变化。

我试图避免让事情变得太复杂。理想情况下，我会将这个公式传达给部门其他成员，以便他们在需要时使用。

我将使用 SUBSTITUTE 和 LEN，但我需要一些帮助来保持其清洁。

答案1

我将在这里介绍几个可供选择的选项，但它们都依赖于正则表达式。也可以不使用正则表达式，但我认为这可能需要更多时间。

[选项1 -https://regex101.com/]
访问页面https://regex101.com/
将风格设置为“pcre (php)” - 这是默认选项 正则
表达式： (?<=;)(#\d+;#)|(;#\d+$)标志：g
测试字符串：Mary One;#123;#Bob Two;#2345;#Charles Three;#445
替换：//一个空格（或可以留空）
输出：Mary One; Bob Two; Charles Three

输出需要修剪，因为末尾有一个额外的空格。

[选项 2 - LibreOffice Calc（便携版）]
Calc“查找和替换”窗口允许使用正则表达式。Ctrl

+H
查找：(?<=;)(#\d+;#)|(;#\d+$)
替换为：一个空格或空白
在“其他选项”部分选中“正则表达式”
单击“全部替换”按钮。

[选项 3-Notepad++]
我在这里提到 N++ 是因为它在处理多种文本时确实非常强大。它的强大功能主要体现在众多插件上。但在这里我将提到一个标准的替换窗口。正则表达式允许拆分匹配的文本部分。这称为分组。每个组可以有一个名称，并且该名称可用于替换。Ctrl

+H → 替换选项卡
环绕：已选中
搜索模式：正则表达式
查找内容：(?'name_surname'[^;#]+\s[^;#]+;)(#\d+(;#|$))
替换为：$+{name_surname}
单击按钮“全部替换”

含义：
“查找内容”可查找所有内容 — 想要的和不需要的文本。
“替换为”仅用想要的部分替换原始文本。

[选项 4 - Excel – 用户定义函数 (VBA)]
打开 Visual Basic 编辑器 (Alt+F11)
添加参考：
工具 --> 参考 --> Microsoft VBScript Regular Expressions 5.5

Option Explicit
Function leaveNames(CellValue As Variant)
    Dim RegEx As RegExp
    Dim Expr As String
    Set RegEx = New RegExp
    Expr = "(#\d+;#)|(;#\d+$)"
    RegEx.Global = True
    RegEx.IgnoreCase = False
    RegEx.MultiLine = False
    RegEx.Pattern = Expr
    leaveNames = RegEx.Replace(CellValue, "")
End Function


[概括]
我试图专注于相当快速但功能齐全的解决方案，所以我想可以从中得到一些东西。

答案2

我尝试了这个公式。效果不太好。不过，我相信还有其他方法可以做到这一点。

此公式将获取单元格 A1 中的字符串并删除#123和#2345字符串。如果您愿意，您可以随时进一步扩展该技术。它会找到“#”的出现位置，然后将字符串分解为更小的部分。

=LEFT(A1,SEARCH("#",A1,1)-1) &  MID(RIGHT(A1,LEN(A1)-SEARCH("#",A1,1)),SEARCH("#",RIGHT(A1,LEN(A1)-SEARCH("#",A1,1)),1)+1,SEARCH("#",MID(RIGHT(A1,LEN(A1)-SEARCH("#",A1,1)),SEARCH("#",RIGHT(A1,LEN(A1)-SEARCH("#",A1,1)),1)+1,LEN((A1))),1)-1)

它既不干净，也不容易传染。

我建议使用用户定义函数。 这里是一篇解释如何设置和分发用户函数的文章。尝试一下 UDF，如果您对该过程有任何疑问，请提出。

相关内容