Strip HTML : How to Remove HTML tags from a String in VBA
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)
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:
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
- VBA: Reverse a string It is very easy to get the Reverse of a...
- How to Extract Email ID from a String in Cell If you want to extract email ID from a String...
- Sorting a String stored in a cell of Excel By using Excel Macro, we can sort a String which...
- Oracle Connection String in VBA Using Excel Macros (VBA) you can connect to any Databases...
- How to connect to Access Database – ADO Connection String Using Excel Macros (VBA) you can connect to any Databases...