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
My name is Vishwamitra Mishra. Friends Call me Vishwa. This blog is authored by me. I am an Excel Geek. Well, this blog talks a lot about my passion in Excels & Macros so I’ll not talk about it :) I am very much passionate about traveling & quite recently discovered that I am a good photographer too..:P
AND GET A FREE!
E-BOOK FOR EXCEL VBA BEGINNERS
DON'T MISS ANY NEW ARTICLE !
Get your FREE! E-Book & Have Every New Article Delivered Straight To Your Email-Box