Convert Formatted Cell in Excel to HTML
To get the font and formatting style of a text in worksheet follow these methods.
- Excel Worksheet Cell format – Single Format
- 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,270 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
Could not make payment. Paypal refused to process the card payment. Is there any other way to pay?