How to Convert HTML to Text in Cells in Excel?

MS Excel Microsoft Technologies Computers

Have you ever attempted to convert HTML text to plain text in Excel? If we try to complete this manually, then it can be a time-consuming and inaccurate process. The difference between the html text and normal text is that the html text has a header and footer, which are represented inside "< >". If those texts are removed from the HTML, they will be treated as plain text. In this article, we will learn how to convert HTML to text in Excel cells. In this tutorial, we will complete this task using the VBA application.

Convert HTML to Text in Cells

Here we will first create a VBA module, enable the references, and run the code to complete the process. Let's take a look at a simple procedure for converting html to text in Excel cells.

Step 1

Let us consider an Excel sheet where the data is a list of HTML, similar to the below image.

Now right-click on the sheet name and select view code to open the VBA application, then click on insert and select module.

Right click > view code > insert > module

Step 2

Then type the below-mentioned programme into the text box as shown in the below image.

Program

Sub RemoveHTMLTags()
'Updated By Nirmal
    Dim xRg As Range
    Dim xCell As Range
    Dim xStr As String
    Dim xRegEx As RegExp
    Dim xMatch As Match
    Dim xMatches As MatchCollection
    Set xRegEx = New RegExp
    Application.EnableEvents = False
    Set xRg = Cells.SpecialCells(xlCellTypeConstants)
    With xRegEx
        .Global = True
        .Pattern = "<(""[^""]*""|'[^']*'|[^'"">])*>"
    End With
    For Each xCell In xRg
        xStr = xCell.Value
            Set xMatches = xRegEx.Execute(xCell.Text)
            For Each xMatch In xMatches
                xStr = Replace(xStr, xMatch.Value, "")
            Next
        xCell.Value = xStr
    Next
   Application.EnableEvents = True
End Sub