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

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. Oracle Connection String in VBA Using Excel Macros (VBA) you can connect to any Databases...
  4. Sorting a String stored in a cell of Excel By using Excel Macro, we can sort a String which...
  5. How to connect to Access Database – ADO Connection String Using Excel Macros (VBA) you can connect to any Databases...

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

  1. [...] from a cell UDF to Extract Special Characters from a cell UDF to Extract Alphabets from a cell UDF to Strip/Remove HTML tags UDF to Extract email ID from a [...]

  2. 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-t…

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

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

  4. 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.

  5. Fabio says:

    Great code! Thanks so much!

  6. 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

Leave a Reply

Comments

comments