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.
- Type ALT+F11
- 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
- Paste the function from above – save it – (CTRL+S ) - you can choose not to save the macro with the Excel sheet
- 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.