In this article I am going to write an User Defined Function (UDF) which is used to remove all HTMLs from a String Stored in Cell. It can be used as normal Inbuilt Formulas of Excel.

For writing this function i have used VBScript Regular Expression to Replace all the HTML tags with BLANK “”. In code you can see i have defined a regular expression to replace all HTML tags.

Regular Pattern which can be used to replace all HTML tags is : “<[^>]+>”

Follow the below Steps to create this UDF (User Defined Function):
1. Open Visual Basic Editor (Alt+ F11)
2. Open an Existing Module or add a New Module
3. Copy Paste the below Code and Save the Excel.

Function – Input as Cell Address


Function StripHTML(cell As Range) As String
 Dim RegEx As Object
 Set RegEx = CreateObject("vbscript.regexp")

 Dim sInput As String
 Dim sOut As String
 sInput = cell.Text
 
 With RegEx
   .Global = True
   .IgnoreCase = True
   .MultiLine = True
.Pattern = "]+>" 'Regular Expression for HTML Tags.
 End With

 sOut = RegEx.Replace(sInput, "")
 StripHTML = sOut
 Set RegEx = Nothing
End Function

4. Now Go to any Cell of any Sheet of your Workbook and use this formula =stripHTML(Cell)

UDF - Remove HTML from String

UDF - Remove HTML from String


Function – Pass whole String with HTML

Below function can be basically used in VBA while writing some Macro.
For example: At some point of time you recieve a string from some resource which has HTML tags with it and you want to remove all of them and want HTML tags free String then simply use this function.

Syntax To Use this Function in your Macro:


htmlFreeString=stripHTML("withHTMLString")

Function StripHTML(sInput As String) As String
 Dim RegEx As Object
 Set RegEx = CreateObject("vbscript.regexp")

 Dim sOut As String
 With RegEx
   .Global = True
   .IgnoreCase = True
   .MultiLine = True
.Pattern = "]+>" 'Regular Expression for HTML Tags.
 End With

 sOut = RegEx.Replace(sInput, "")
 StripHTML = sOut
 Set RegEx = Nothing
End Function

cover3d_0-89071700_1484285537__1_

Join over 10, 000+ Excel VBA Enthusiasts & get this FREE e-Book Now!