Remove HTML tags from string in Excel VBA

Question

I want to remove all HTML tags from a string in Excel VBA.

For example:

before_text = "text1 <br> text2 <a href = 'www.data.com' id = 'data'>text3</a> text4"

after_text = RemoveTags(before_text)

Result:

after_text = "text1  text2 text3 text4"

Possible duplicate of Stripping HTML From A String

user6722034
– user6722034

2017-11-18 14:48:20 +00:00
Commented Nov 18, 2017 at 14:48 — user6722034
– user6722034, Commented Nov 18, 2017 at 14:48

Zhihar · Accepted Answer · 2017-11-18 15:27:27Z

5

vbscript.regexp

Code:

Function RemoveHTML(text As String) As String
    Dim regexObject As Object
    Set regexObject = CreateObject("vbscript.regexp")

    With regexObject
        .Pattern = "<!*[^<>]*>"    'html tags and comments
        .Global = True
        .IgnoreCase = True
        .MultiLine = True
    End With

    RemoveHTML = regexObject.Replace(text, "")
End Function

answered Nov 18, 2017 at 15:27

Zhihar

1,4483 gold badges27 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Echilon · Accepted Answer · 2020-08-26 10:02:57Z

1

Building on @zhihar's reply, to make this strip all HTML from the selected cell you can iterate through the selection.

Function RemoveHTML(text As String) As String
    Dim regexObject As Object
    Set regexObject = CreateObject("vbscript.regexp")

    With regexObject
        .Pattern = "<!*[^<>]*>"    'html tags and comments
        .Global = True
        .IgnoreCase = True
        .MultiLine = True
    End With

    RemoveHTML = regexObject.Replace(text, "")
End Function


Sub StripHtmlSelected()
    For Each Cell In Selection
        If Not Cell.HasFormula Then
            Cell.Value = RemoveHTML(Cell.Value)
        End If
    Next Cell
End Sub

answered Aug 26, 2020 at 10:02

Echilon

10.3k33 gold badges138 silver badges226 bronze badges

Collectives™ on Stack Overflow

Remove HTML tags from string in Excel VBA

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related