Convert a Formatted Cell in Excel into HTML Tag using VBA

Convert Formatted Cell in Excel to HTML

To get the font and formatting style of a text in worksheet follow these methods.

1. Excel Worksheet Cell format – Single Format
2. Text with Different formats in same cell – Multiple Format

When we type any text in a Worksheet, we change the default format to give importance to some area of text. We can change the format of a text color, background color, font size, font family, underline, bold, make the text italics etc.,

Let’s see how to read these formatting styles of a Excel sheet cell and convert it into its equivalent HTML code.

Excel Worksheet Cell with Single format to HTML

To convert Excel to HTML, when a cell has only single format, then the following VBA code can be used. This VBA code reads the font format property of cell. Then converts each property into its equivalent HTML tag.

To use this code, first insert a new module in VB editor. Then copy paste this code. (Press alt F11 -> Menu -> Insert -> Module).

'Code from Officetricks.com - Convert Cell in Excel to HTML Format
Function Convert_Excel_Cell_To_HTML_Single_Format(iCell As Range) As String
    Dim flgSubscript As Boolean, flgSuperscript As Boolean, flgStrikethrough As Boolean
    Dim flgBold As Boolean, flgItalic As Boolean, flgUline As Boolean
    Dim htmlTagText As String, htmlTagStart As String, htmlTagEnd As String
    htmlTagText = ""
    
    If iCell.Font.Bold Then
        htmlTagStart = htmlTagStart & "<b>"
        htmlTagEnd = "</b>" & htmlTagEnd
    End If
    If iCell.Font.Underline > 0 Then
        htmlTagStart = htmlTagStart & "<u>"
        htmlTagEnd = "</u>" & htmlTagEnd
    End If
    If iCell.Font.Italic Then
        htmlTagStart = htmlTagStart & "<i>"
        htmlTagEnd = "</i>" & htmlTagEnd
    End If
    
    If iCell.Font.Subscript Then
        htmlTagStart = htmlTagStart & "<sub>"
        htmlTagEnd = "</sub>" & htmlTagEnd
    End If
    If iCell.Font.Superscript Then
        htmlTagStart = htmlTagStart & "<sup>"
        htmlTagEnd = "</sup>" & htmlTagEnd
    End If
    If iCell.Font.Strikethrough Then
        htmlTagStart = htmlTagStart & "<del>"
        htmlTagEnd = "</del>" & htmlTagEnd
    End If
    
    htmlTagText = "<p style='font-family:" & Chr(34) & iCell.Font.Name & Chr(34)
    htmlTagText = htmlTagText & ";font-size:" & iCell.Font.Size & "pt"
    htmlTagText = htmlTagText & ";color:#" & ColorIdx_to_RGB(iCell.Font.Color) & "'>"
    htmlTagText = htmlTagText & iCell.Value & "</p>"
    htmlTagText = htmlTagStart & htmlTagText & htmlTagEnd
    
    Convert_Excel_Cell_To_HTML_Single_Format = htmlTagText
End Function

Function ColorIdx_to_RGB(cColorIndex As String) As String
    Dim cRed As String, cGreen As String, cBlue As String
    cColorIndex = Right("000000" & VBA.Hex(cColorIndex), 6)
    cBlue = Left(cColorIndex, 2)
    cGreen = Mid(cColorIndex, 3, 2)
    cRed = Right(cColorIndex, 2)
    ColorIdx_to_RGB = cRed & cGreen & cBlue
End Function

The function takes any cell as its parameter. To test this code, enter any text in cell A1 with bold,underlined and color it, then in cell Be enter this formula.

‘=Convert_Excel_Cell_To_HTML_Single_Format(A1)’

Note: Function ‘ColorIdx_to_RGB‘ from above code get the Color code and converts into RGB code in VBA.

Convert Cell in Excel to HTML – Multiple Format Text

Consider you have text with multiple formats in a cell like the one below.

“This Text has multiple formats and needs to converted to HTML“.

To convert this text with so many font formats, the previous methods wont work. To make it work, we have to read format of each character in the cell, get its format (whether bold, italics, underlined, superscript, subscript or strikethrough). Then convert those formats into HTML equivalent.

Download the below Excel app that can convert such complex text formats into HTML tag.

Download Excel App to Convert Formatted Cell to HTML using VBA Downloaded 1,485 Times

If you are satisfied with the functionality and working of this VBA code, then you can the Excel Macro VBA code from this link at a cheap and affordable price.

For any queries or additional functionalities leave a comment or email to author@officetricks.com