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
