IntFontSizeStartPos = intFontSizeStartPos + 1 If Mid$(strHTML, intFontSizeStartPos, 1) = "+" Then ' Clear any bold/underline/italic/superscript/subscript formatting from cellĭebug.Print Mid$(strHTML, intFontSizeStartPos, intFontSizeEndPos - intFontSizeStartPos + 1) StrActualText = strActualText & Mid$(strHTML, intSrcPos, 1) If LCase(Mid$(strHTML, intSrcPos, Len(varTag))) = varTag Then StrHTML = Left$(strHTML, Len(strHTML) - 4)ĭo While intSrcPos Len(strHTML) Then Exit Do While LCase(Right$(strHTML, 4)) = "" Or LCase(Right$(strHTML, 4)) = ""
' Remove unhandled/unneeded tags, convert and tags to line feeds ' NOTE: Font Sizes not handled perfectly per HTML standard, but I find this method more useful!ĭim strActualText As String, intSrcPos As Integer, intDestPos As Integer, intDestSrcEquiv() As Integerĭim varyTags As Variant, varTag As Variant, varEndTag As Variant, blnTagMatch As Booleanĭim intStartPos As Integer, intEndPos As Integer, intActualStartPos As Integer, intActualEndPos As Integerĭim intFontSizeStartPos As Integer, intFontSizeEndPos As Integer, intFontSize As Integer ' Adds converts text formatted with basic HTML tags to formatted text in an Excel cell Hope others find this useful: Public Sub AddHTMLFormattedText(rngA As Range, strHTML As String, Optional blnShowBadHTMLWarning As Boolean = False) Ultimately, I came up with a routine that'll handle simple tags and not use the "native" Unicode converter that is causing the issue with merged fields. I tried a few tweaks (unmerge/remerge, etc.) but then Excel dropped anything below a break, so that was a dead end. I was disappointed that Excel doesn't let us paste to a merged cell and also pastes results containing a break into successive rows below the "target" cell though, as that meant it simply doesn't work for me. Private Sub Worksheet_Change(ByVal Target As Range, ByVal sht As Worksheet)ĭim objData As DataObject ' Set a reference to MS Forms 2.0 Note, if your innerHTML is all numbers eg '12345', HTML formattingĭosen't fully work in excel as it treats number differently? but add a character egĪ trailing space at the end eg.
Anyway this should be faster than starting If the IE example doesn't work use this one.