Excel VBA: How to remove substrings from a cell?

Question

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:

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

Any help will be appreciated. Thanks.

In this case, I'd recommend the use of [Regular Expressions (RegEx)](http://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops) to remove the unwanted substrings. — Ralph, Aug 10 '16 at 11:13
Will the <" "> actually be there? Or are you trying to prevent having the same word in a row twice — David G, Aug 10 '16 at 12:21

score 3 · Answer 1 · answered Aug 10 '16 at 12:02

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 ">

@PatrickLepelletier - regexes are a steep learning curve, but it is worth the effort. The regex in my answer basically says match anything in `<"` and `">` except the characters `<` `>` and `"`. The regex looks a bit more complicated than it needs to because of the doubled `""` in the pattern which are required because VBA uses `"` for strings and the pattern also needs `"` in its value. The pattern is actually `<"[^<>"]*">` without this complication. You can try it http://www.regex101.com — Robin Mackenzie, Aug 11 '16 at 08:30

score 2 · Accepted Answer · answered Aug 10 '16 at 12:27

Assuming the text in cell A1, then try this code

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

score 1 · Answer 3 · answered Aug 10 '16 at 11:36

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

score -1 · Answer 4 · answered Aug 10 '16 at 11:46

-1

U can Use Replace function

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

answered Aug 10 '16 at 11:46

Takdir Madhavi

39
3

That's not what she's asking at all. – David G Aug 10 '16 at 12:22

Excel VBA: How to remove substrings from a cell?

4 Answers4

Linked