'Converting html special character in excel

Could anyone please suggest a function/formula used in worksheet to convert html special character to HTML entity, thanks
E.g.
™ to ™
® to ®

excel

Solution 1:^[1]

The answer to this question is a two part.

Do you only need to convert a certain set of these special chars?
Do you need to convert All supported?

Answer 1:

Public Function ConvertHTMLTag(data As String) As String
    data = Replace(data, "&trade;", "™")
    data = Replace(data, "&reg;", "®")
    ConvertHTMLTag = data
End Function

Answer 2:

Repeat for all chars in http://www.webmonkey.com/2010/02/special_characters/

To make this a bit easier, I would try to put this list in to an Excel sheet with in two columns. One for the special tag and the other with it's evaluated char.

Write a formula in a 3rd column to create your code for you...

="data = Replace(data, "&Char(34)&A1&Char(34)&", "&Char(34)&A2&Char(34)&")"

Once you have your VBA code you've created in Excel, a simple copy and paste in to the function above will do the trick.

Solution 2:^[2]

Use this function to encode from html special character to string

Function HTMLToCharCodes(ByVal s As String) As String

    With New MSXML2.DOMDocument60
        .LoadXML "<p>" & s & "</p>"

        HTMLToCharCodes = .SelectSingleNode("p").nodeTypedValue
    End With

End Function

Input: &, return: &

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1	Allen Gammel
Solution 2	Unicco

'Converting html special character in excel

Solution 1:[1]

Solution 2:[2]

Sources

Related Questions

Solution 1:^[1]

Solution 2:^[2]