HTML Text with tags to formatted text in an Excel cell
Yes it is possible :) In fact let Internet Explorer do the dirty work for you ;)
TRIED AND TESTED
MY ASSUMPTIONS
- I am assuming that the html text is in Cell A1 of Sheet1. You can also use a variable instead.
- If you have a column full of html values, then simply put the below code in a loop
CODE
Sub Sample() Dim Ie As Object Set Ie = CreateObject("InternetExplorer.Application") With Ie .Visible = False .Navigate "about:blank" .document.body.InnerHTML = Sheets("Sheet1").Range("A1").Value .document.body.createtextrange.execCommand "Copy" ActiveSheet.Paste Destination:=Sheets("Sheet1").Range("A1") .Quit End WithEnd SubSNAPSHOT
You can copy the HTML code to the clipboard and paste special it back as Unicode text. Excel will render the HTML in the cell. Check out this post http://www.dailydoseofexcel.com/archives/2005/02/23/html-in-cells-ii/
The relevant macro code from the post:
Private Sub Worksheet_Change(ByVal Target As Range) Dim objData As DataObject Dim sHTML As String Dim sSelAdd As String Application.EnableEvents = False If Target.Cells.Count = 1 Then If LCase(Left(Target.Text, 6)) = "<html>" Then Set objData = New DataObject sHTML = Target.Text objData.SetText sHTML objData.PutInClipboard sSelAdd = Selection.Address Target.Select Me.PasteSpecial "Unicode Text" Me.Range(sSelAdd).Select End If End If Application.EnableEvents = TrueEnd Sub
I ran into the same error that BornToCode first identified in the comments of the original solution. Being unfamiliar with Excel and VBA it took me a second to figure out how to implement tiQU's solution. So I'm posting it as a "For Dummies" solution below
- First enable developer mode in Excel: Link
- Select the Developer Tab > Visual Basic
- Click View > Code
- Paste the code below updating the lines that require cell references to be correct.
- Click the Green Run Arrow or press F5
Sub Sample() Dim Ie As Object Set Ie = CreateObject("InternetExplorer.Application") With Ie .Visible = False .Navigate "about:blank" .document.body.InnerHTML = Sheets("Sheet1").Range("I2").Value 'update to the cell that contains HTML you want converted .ExecWB 17, 0 'Select all contents in browser .ExecWB 12, 2 'Copy them ActiveSheet.Paste Destination:=Sheets("Sheet1").Range("J2") 'update to cell you want converted HTML pasted in .Quit End WithEnd Sub