Below is the code for a quick, easy custom Excel function to remove HTML tags and code from a string. This function can be used directly in a sheet, or be called from within VBA.

Examples

The function will strip complete html tags or if the string has a partial tag (starts with a tag that is open, or ends with a tag that it still open), it will remove that as well.

html_tag_remover

Function Code

The code is pretty simple and uses the built-in ability of Excel to extract the outer text of an HTML object. Then, it simply accounts for the possibility of starting with or ending with an open tag and removes those as well.

Function remove_tags(html As String) As String
    If InStr(1, html, ">", 1) < InStr(1, html, "<", 1) Or (InStr(1, html, ">", 1) <> Null And InStr(1, html, "<", 1) = Null) Then
        html = Right(html, Len(html) - InStr(1, html, ">", 1))
    End If
    If InStrRev(html, "<", -1, 1) > InStrRev(html, ">", -1, 1) Or (InStrRev(html, "<", -1, 1) <> Null And InStrRev(html, ">", -1, 1) = Null) Then
        html = Left(html, InStrRev(html, "<", -1, 1) - 1)
    End If
    With CreateObject("htmlfile")
        .Open
        .write html
        .Close
         remove_tags = .body.outerText
    End With
End Function

Let me know in the comments below if you have any questions on this!

Related posts

2 thoughts on “Custom Excel Function to remove HTML tags”

  1. Wes

    Thanks for posting this. It worked like a champ!

    1. Happy to help! I always love saving other people the time of starting from scratch.

Leave a Comment