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

I have a cell value like this:

我有这样的单元格值：

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

There are some words repeated in this part <" ">.

在这部分中重复了一些单词<“”>。

I want use Excel VBA to change the cell value to:

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

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

Any help will be appreciated. Thanks.

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

4 个解决方案

#1

Assuming the text in cell A1, then try this code

假设单元格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

#2

Following up on the suggestion to use regular expressions, here's an example:

继续使用正则表达式的建议，这是一个例子：

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

Produces this output:

生成此输出：

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.

Diagram of regular expression:

正则表达式图：

Which can be explained as finding a string that:

这可以解释为找到一个字符串：

begins with <"
以<“开头
contains anything apart from the characters <, > and "
包含除<，>和“字符之外的任何内容
ends with ">
以“>结束

#3

Select the cells containing your text and run this short macro:

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

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

#4

-1

U can Use Replace function

你可以使用替换功能

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

#1