Skip to main content

HTML as String in Excel

When handling a large chunk of data in Excel, let's say while importing a JSON file to excel for processing, certain data may be of HTML type.

Example "Bath robes & pounch" where & stands for &

This can be fixed by use of macros in Excel.

  1. Type ALT+F11
  2. In the File menu, go Insert > Module
Public Function HtmlDecode(StringToDecode As Variant) As String
    Set oMSHTML = CreateObject("htmlfile")
    Set e = oMSHTML.createElement("T")
    e.innerHTML = StringToDecode
    HtmlDecode = e.innerText
End Function
  1. Paste the function from above – save it – (CTRL+S ) - you can choose not to save the macro with the Excel sheet
  2. Switch back to Excel, then use it as a function in Excel as shown

Suppose A1 has data - "Bath robes & pounch"

Then in A2 cell use the below function

=HtmlDecode(A1)

A2 cell now has "Bath robes & pounch"

When using the function over a larger dataset, like a complete column, it takes some processing time, so be patient and wait.