CODEWITHSUNDEEP
excelexcel-formulaexcel-2010excel-2007vba
Prdp
Prdp

Reputation: 21

How to remove text between two strings, including identifiers?

I have a requirement to remove the text between two strings.
An example of the text is:

Abc%678x”cv ","@metadata":{abxkl "DataArray"}},{"columnName":"

The requirement is to start removing text from ,"@met till "}

The requirement is to use ,"@met and "} as start and end identifiers and remove text between them including the identifiers.

There could be multiple occurrence of this start and end identifiers within the file.

The output should look like this:

Abc%678x”cv "},{"columnName":"

How to write an Excel formula or simple VBA script to remove text between two strings, including identifiers?

Upvotes: 2

Views: 2071

Answers (4)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60224

This can be done easily using VBA and regular expressions:

Option Explicit
Function RemoveBetweenDelimiters(S As String) As String
    Dim RE As Object

Set RE = CreateObject("vbscript.regexp")
With RE
    .Global = True
    .ignorecase = True
    .Pattern = ",""@met[^}]+}"
    RemoveBetweenDelimiters = .Replace(S, "")
End With

End Function

The regex interpretation:

,"@met[^}]+}

,"@met[^}]+}

Options: Case insensitive; ^$ match at line breaks

Created with RegexBuddy

Upvotes: 0

Olly
Olly

Reputation: 7891

Formula:

=LEFT(A1,FIND(",""@met",A1)-1)&RIGHT(A1,LEN(A1)-FIND("}",A1,FIND("""@met",A1)))

VBA function:

Function RemoveBetweenSeparators( _
    ByVal MyString As String, _
    ByVal SepL As String, _
    ByVal SepR As String) _
    As String

    Dim sL As String
    Dim sR As String

    sL = Split(MyString, SepL)(0)
    sR = Replace(MyString, sL, "")
    sR = Replace(sR, Split(sR, SepR)(0) & SepR, "")

    RemoveBetweenSeparators = sL & sR
End Function

Which can be used like this:

=RemoveBetweenSeparators(A1,"""@meta","}")

EDIT: I also missed the 'multiple occurences' requirement, first time round! That makes it a little trickier, but try this:

Function RemoveBetweenSeparatorsMultiple( _
    ByVal MyString As String, _
    ByVal SepL As String, _
    ByVal SepR As String) _
    As String

    Dim sOut As String
    Dim sL As String

    Do Until InStr(MyString, SepL) = 0
        sL = Split(MyString, SepL)(0)
        sOut = sOut & sL
        MyString = Replace(MyString, sL & SepL, "", 1, 1)
        sL = Split(MyString, SepR)(0)
        MyString = Replace(MyString, sL & SepR, "", 1, 1)
    Loop

    RemoveBetweenSeparatorsMultiple = sOut & MyString

End Function

Upvotes: 2

newacc2240
newacc2240

Reputation: 1425

My apology, didn't notice that there could be multiple occurrence. I'll edit my answer later.

Assuming the original text is stored in A1.

A2=LEFT(A1,FIND(",""@met",A1)-1)&RIGHT(A1,LEN(A1)-FIND("""}",A1)-1)

Note: If you need to force excel treat a double quote mark as a normal text, you have to type two " for representing a ".

If there may be multiple occurrence, try this

Private Function RemoveText(ByVal tgtString As String, ByVal StartText As String, ByVal EndText As String) As String
    Do While InStr(1, tgtString, StartText) > 0
        tgtString = Left(tgtString, InStr(1, tgtString, StartText) - 1) & Right(tgtString, Len(tgtString) - InStr(1, tgtString, EndText) - 1)
    Loop

    RemoveText = tgtString
End Function

Private Sub test()
    'remove certain string in A1 and store the result in A2
    Range("A2").Value = RemoveText(Range("A1").Value, ",""@met", """}")
End Sub

Upvotes: 1

Joe Phi
Joe Phi

Reputation: 350

maybe somthing like this (not tested though!) :

    Function cleanedStr (inpStr as String; beginDel as string; endDel as Str) as String
    Dim idx     as long
    Dim take    as boolean
    Dim outStr  as String
    Dim myCh    as String

        take = true
        outStr = ""
        for idx = 1 to len(inpStr)
            myCh = mid(inpStr, idx, 1)
            if myCh = beginDel then take = false
            if take then 
                outStr = outStr & myCh
            else
                if myCh = endDel then take = true
            end if
        next idx        
        cleanedStr = outStr    
    end Function

Mind, the begin-identifier is 1 character only.
beginDel would be @ and endDel would be }

Upvotes: 0

Related Questions