HTML Text with tags to formatted text in an Excel cell

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 = True

End 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

  1. First enable developer mode in Excel: Link
  2. Select the Developer Tab > Visual Basic
  3. Click View > Code
  4. Paste the code below updating the lines that require cell references to be correct.
  5. 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 With
End Sub

Yes it is possible. In fact let Internet Explorer do the dirty work for you.

MY ASSUMPTIONS

  1. I am assuming that the html text is in Cell A1 of Sheet1. You can also use a variable instead.
  2. 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 With
End Sub

SNAPSHOT

enter image description here

Tags:

Html Parsing

Excel

Vba

Recent Posts