How to Remove Unwanted text from Cells Excel Add-in code example

How to Remove Unwanted text from Cells Excel Add-in code example

Unwanted Text

Which you don’t want to see in your data. Consider you got thousands of rows which are scrapped from Web or some other source which contains repeated unwanted text which you would like to remove to clean the data. I have developed a method which will do job for you.

Syntax

RemoveUnwantedCodeVBAOVERALL(oRange, startChar, endChar)

Parameters

  • oRange: a mandatory parameter which is range type. User needs to pass range containing unwanted data.
  • startChar: a mandatory parameter takes one character as starting point from where you would like to remove unwanted data from.
  • endChar: a mandatory parameter takes one character as ending point up to that you would like to remove unwanted data.

Example data

Consider below data where I want to remove data which is blocked between “<“ and “>” as shown below:

Code example

Public Sub RemoveUnwantedCodeVBAOVERALL(oRange As Range, startChar As String, endChar As String)
    Dim oCell As Range
    For Each oCell In oRange.Cells
        Dim oStr As String
        If oCell.HasFormula = False Then
            oStr = oCell.Value
            Dim oSPosition As Long
            Dim oEPosition As Long
            oSPosition = InStr(1, oStr, startChar)
            If oSPosition > 0 Then
                oEPosition = InStr(1, oStr, endChar)
                If oEPosition > 0 Then
                    Dim oReplaceStr As String
                    oReplaceStr = Mid(oStr, oSPosition, (oEPosition - oSPosition) + 1)
                    oCell = Replace(oStr, oReplaceStr, "")
                End If
            End If
        End If
    Next oCell
    
    'Cleanup
    If Not oCell Is Nothing Then
        Set oCell = Nothing
    End If
End Sub

Calling method

Public Sub CallRemoveUnwantedText()
    Call RemoveUnwantedCodeVBAOVERALL(Selection, "<", ">")
End Sub

Output

Next >> Range Parse Method Excel VBA with example

Leave a Reply

Your email address will not be published.