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.
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!
Thanks for posting this. It worked like a champ!
Happy to help! I always love saving other people the time of starting from scratch.