Excel VBA: How to remove substrings from a cell (with same beginning and ending symbol)?

Question

I have a cell value like this:

This is a line. /delete words in the area /keep this part

Lets say the cell is A1, The cell value contains two /s. I want to use excel VBA to delete the letters between them and change the cell value to:

This is a line. keep this part

Actually I asked a similar question before: Excel VBA: How to remove substrings from a cell?

There I had two different symbols to locate the substring, but here I have two same symbols. Then I dont know how to rewrite the code.

Any help will be appriated. Thanks!

score 3 · Accepted Answer · answered Aug 21 '16 at 01:49

You don't mention whether you would prefer an Excel function or a VBA solution. Both have fairly straightforward answers. In both cases you could keep things simple by using a find function that locates the string index of your search character. In Excel functions it's called Find and in VBA a very close equivalent is InStr. You would just find both occurrences of your separator and join the left and right edges of the remaining string.

In Excel, the function could be where "A1" is your cell address:

=LEFT(A1,FIND("/",A1)-1) & RIGHT(A1,LEN(A1)-FIND("/",A1,FIND("/",A1)+1))

Or in VBA, the equivalent would be:

Public Function TextOutsideDelimeters(txt As String, sep As String) As String
    Dim pt1 As Long
    Dim pt2 As Long

    pt1 = InStr(txt, sep)
    pt2 = InStr(pt1 + 1, txt, sep)

    TextOutsideDelimeters = Left(txt, pt1 - 1) & _
                            Right(txt, Len(txt) - pt2)


End Function

score 2 · Answer 2 · answered Aug 21 '16 at 01:30

How about something like this:

Option Explicit

Public Sub tmpSO()

Dim i As Long
Dim strTMP As String
Dim strFinal As String
Dim strArray() As String

strTMP = "This is a line. /delete words in the area /keep this part /yet another part to delete / keep this one too / delete me."

strArray = Split(strTMP, "/")
For i = LBound(strArray) To UBound(strArray)
    If i Mod 2 = 0 And i <> UBound(strArray) Then
        strFinal = strFinal & strArray(i)
    End If
Next i
Debug.Print strFinal

End Sub

score 2 · Answer 3 · answered Aug 21 '16 at 02:43

2

[A1].Replace "/*/", "", xlPart

answered Aug 21 '16 at 02:43

Slai

22,144
5
45
53

Excel VBA: How to remove substrings from a cell (with same beginning and ending symbol)?

3 Answers3