Strip HTML : How to Remove HTML tags from a String in VBA

December 29, 2011| Posted by : | Filled in Excel Functions, Excel Macro

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

Comments

comments

Related posts:

  1. VBA: Reverse a string It is very easy to get the Reverse of a...
  2. How to Extract Email ID from a String in Cell If you want to extract email ID from a String...
  3. Sorting a String stored in a cell of Excel By using Excel Macro, we can sort a String which...
  4. Oracle Connection String in VBA Using Excel Macros (VBA) you can connect to any Databases...
  5. How to connect to Access Database – ADO Connection String Using Excel Macros (VBA) you can connect to any Databases...

Subscribe To Get New Posts delivered directly to your Mailbox

9 Responses to “Strip HTML : How to Remove HTML tags from a String in VBA”

  1. Rahul Kumar says:

    Hi i used this but inspite of new line it showing / in the string

    • Vishwamitra Mishra Vishwamitra Mishra says:

      Hi Rahul,
      I would appreciate if you Can explain me with an example, how you want and how the currect code is behaving for you.
      Thanks,
      Vish

  2. Fabio says:

    Great code! Thanks so much!

  3. Dali says:

    With the “Function – Pass whole String with HTML” where do you input the syntax? Under a new module or? It is not working for me

    • Vishwamitra Mishra Vishwamitra Mishra says:

      What Syntax do you want to pass?? In that function you just need to pass the String with HTML tags and it will remove all the HTML tags and return you a string without HTML tags.

  4. [...] paragraph.</p></body></html>" stringwithpipe = StripHTML(htmlstring) End Sub Bron __________________ gr. [...]

  5. Hien says:

    Hi, thanks for your post!
    How can we improve your function so that we can have:
    This is a test. Will this text be bold or italic
    to become (1):
    This is a test. Will this text be bold or italic
    instead of (2)
    This is a test. Will this text be bold or italic

    when using =stripHTML(Cell) formula.

    The resulted (1) will be rich formatted instead of only remove html tags.

    For solving the problem of
    http://stackoverflow.com/questions/9999713/html-text-with-tags-to-formatted-text-in-an-excel-cell

    I am new in this field. So, I would hope to get understandable answer. Thank You in advance.

Leave a Reply

  • About
  • Downloads
  • Excel Formula
  • Excel Tips
  • Excel Macro Tutorial
  • VBA Functions
  • Archives
  • Contact
  • Excel Formula
  • Excel Functions
  • Excel Macro
  • Excel Macro Basics
  • Excel Macro Tutorial
  • Excel Tips
  • HP QC
  • Interesting VBA Functions
  • New to Excel Macro ?
  • Personal
  • Popular Articles
  • 2014
  • 2013
  • 2012
  • 2011