Excel VBA：如何从单元格中删除子字符串？

Question

我有一个单元格的值如下：

This is a <"string">string, It should be <"changed">changed to <"a"> a number.

在这段文字中有一些重复的词，即<" ">。

我希望使用Excel VBA将单元格值更改为：

This is a string, It should be changed to a number.

任何帮助都将不胜感激。谢谢。

- Rita

---

在这种情况下，我建议使用正则表达式（RegEx）来删除不需要的子字符串。 - Ralph

这个符号 <" "> 实际上会在那里吗？还是你试图防止同一个单词连续出现两次？ - David G

Answer 1

跟进使用正则表达式的建议，这里有一个例子：

Option Explicit

Sub RemoveByRegexWithLateBinding()

    Dim strIn As String
    Dim strOut As String
    Dim objRegex As Object

    'input
    strIn = "This is a <""string"">string, It should be <""changed"">changed to <""a""> a number."
    Debug.Print "Input:" & vbCr & strIn

    'create and apply regex
    Set objRegex = CreateObject("VBScript.RegExp")
    objRegex.Pattern = "<""[^<>""]*"">"
    objRegex.Global = True
    strOut = objRegex.Replace(strIn, "")

    'test output
    Debug.Print "Output:" & vbCr & strOut

End Sub

生成以下输出：

Input:
This is a <"string">string, It should be <"changed">changed to <"a"> a number.
Output:
This is a string, It should be changed to  a number.

正则表达式示意图：

这可以解释为查找一个字符串，该字符串:

• 以<"开头
• 包含除<、>和"之外的任何内容
• 以">结尾

- Robin Mackenzie

---

@PatrickLepelletier - 正则表达式是一个陡峭的学习曲线，但它值得花费精力去学习。我答案中的正则表达式基本上是在<"和">之间匹配除了<、>和"字符以外的任何内容。这个正则表达式看起来比实际需要复杂一些，因为模式中的双引号 "" 是必需的，因为 VBA 使用 " 表示字符串，而模式中也需要 " 。这个模式实际上是 <"[^<>"]*"> ，没有这样的复杂性。你可以在 http://www.regex101.com 上尝试它。 - Robin Mackenzie

Answer 2

假设单元格A1中有文本，则尝试以下代码。

Sub DelDoubleString()
Dim Text As String, Text2Replace As String, NewText As String
On Error Resume Next        'Optional, in case there's no double string to be deleted
Text = Cells(1, 1)

Do
    Text2Replace = Mid$(Text, InStr(Text, "<"), InStr(Text, ">") - InStr(Text, "<") + 1)
    NewText = Application.WorksheetFunction.Substitute(Text, Text2Replace, vbNullString)
    Text = NewText
Loop Until InStr(NewText, "<") = 0

Cells(1, 1) = NewText

End Sub

- Anastasiya-Romanova 秀

Answer 3

选择包含文本的单元格并运行这个简短的宏：

Sub Kleanup()
    Dim d As Range, s As String, rng As Range
    Dim gather As Boolean, L As Long, DQ As String
    Dim i As Long, s2 As String, CH As String

    Set rng = Selection
    DQ = Chr(34)

    For Each r In rng
        s = Replace(r.Text, "<" & DQ, Chr(1))
        s = Replace(s, DQ & ">", Chr(2))
        gather = True
        L = Len(s)
        s2 = ""
        For i = 1 To L
            CH = Mid(s, i, 1)
            If CH = Chr(1) Then gather = False
            If CH = Chr(2) Then gather = True
            If gather And CH <> Chr(2) Then s2 = s2 & CH
        Next i
        r.Value = s2
    Next r
End Sub

- Gary's Student

Answer 4

你可以使用 Replace 函数

ActiveSheet.Cells(1, 1).Value = Replace(ActiveSheet.Cells(1, 1).Value, "String", "Number")

- Takdir Madhavi

---

这完全不是她所问的。 - David G