CODEWITHSUNDEEP
excelvba
Rita
Rita

Reputation: 2317

Excel VBA: How to remove substrings from a cell?

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.

Upvotes: 3

Views: 1075

Answers (4)

Anastasiya-Romanova 秀
Anastasiya-Romanova 秀

Reputation: 3368

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

Upvotes: 2

Robin Mackenzie
Robin Mackenzie

Reputation: 19319

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:

enter image description here

Which can be explained as finding a string that:

  • begins with <"
  • contains anything apart from the characters <, > and "
  • ends with ">

Upvotes: 3

Takdir Madhavi
Takdir Madhavi

Reputation: 39

U can Use Replace function

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

Upvotes: -1

Gary&#39;s Student
Gary&#39;s Student

Reputation: 96753

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

Upvotes: 1

Related Questions