删除出现在两个字符之间的文本 - 多个实例 - Excel [英] Remove text appearing between two characters - multiple instances - Excel

问题描述

在 Microsoft Excel 文件中，我在行中有一个文本，如下所示:

In Microsoft Excel file, I have a text in rows that appears like this:

1. Rc8 {[%emt 0:00:05]} Rxc8 {[%emt 0:00:01]} 2. Rxc8 {[%emt 0:00:01]} Qxc8 {} 3. Qe7#  1-0

我需要删除花括号 { 和 } 中出现的任何文本，包括括号本身.

I need to remove any text appearing within the flower brackets { and }, including the brackets themselves.

在上面的例子中，有三个这样的花括号实例.但有些行可能不止这些.

In the above example, there are three instances of such flower brackets. But some rows might have more than that.

我试过 =MID(LEFT(A2,FIND("}",A2)-1),FIND("{",A2)+1,LEN(A2))

这输出到:{[%emt 0:00:05]}.如您所见，这是这些花括号之间的第一个文本实例.

This outputs to: {[%emt 0:00:05]}. As you see this is the very first instance of text between those flower brackets.

如果我们像这样在 SUBSTITUTE 中使用它: =SUBSTITUTE(A2,MID(LEFT(A2,FIND("}",A2)),FIND("{",A2),LEN(A2)),"")

And if we use this to within SUBSTITUTE like this: =SUBSTITUTE(A2,MID(LEFT(A2,FIND("}",A2)),FIND("{",A2),LEN(A2)),"")

我得到这样的输出:

<代码>1.Rc8 Rxc8 {[%emt 0:00:01]} 2. Rxc8 {[%emt 0:00:01]} Qxc8 {} 3. Qe7# 1-0

如果您已经注意到，只有一个实例被删除了.我如何使它适用于所有实例?谢谢.

If you have noticed, only one instance is removed. How do I make it work for all instances? thanks.

推荐答案

没有 VBA 没那么容易，但还是有办法的.

It is not that easy without VBA, but there is still a way.

或者(按照 yu_ominae 的建议)使用这样的公式并自动填充它:

Either (as suggested by yu_ominae) just use a formula like this and auto-fill it:

=IFERROR(SUBSTITUTE(A2,MID(LEFT(A2,FIND("}",A2)),FIND("{",A2),LEN(A2)),""),A2)

另一种方法是迭代计算(转到选项 -> 公式 -> 选中启用迭代计算"按钮)
现在要在一个单元格中执行此操作，您需要 1 个辅助单元格(在我的示例中，我们将使用 C1)并在 B2 中使用这样的公式并自动向下填充:

Another way would be iterative calculations (go to options -> formulas -> check the "enable iterative calculations" button)
To do it now in one cell, you need 1 helper-cell (for my example we will use C1) and the use a formula like this in B2 and auto-fill down:

=IF($C$1,A2,IFERROR(SUBSTITUTE(B2,MID(LEFT(B2,FIND("}",B2)),FIND("{",B2),LEN(B2)),""),B2))

在 C1 中输入1"，B:B 中的所有公式将显示 A:A 的值.现在转到 C1 并按 del 键几次(您将看到{}"部分消失)，直到所有看起来都像您想要的那样.

Put "1" in C1 and all formulas in B:B will show the values of A:A. Now go to C1 and hit the del-key several times (you will see the "{}"-parts disappearing) till all looks like you want it.

编辑:要通过 VBA 但没有正则表达式，您可以简单地将其放入模块中:

EDIT: To do it via VBA but without regex you can simply put this into a module:

Public Function DELBRC(ByVal str As String) As String
  While InStr(str, "{") > 0 And InStr(str, "}") > InStr(str, "{")
    str = Left(str, InStr(str, "{") - 1) & Mid(str, InStr(str, "}") + 1)
  Wend
  DELBRC = Trim(str)
End Function

然后在工作表中直接使用:

and then in the worksheet directly use:

=DELBRC(A2)

如果你还有任何问题，尽管问;)

If you still have any questions, just ask ;)

这篇关于删除出现在两个字符之间的文本 - 多个实例 - Excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！