How to Convert HTML to Text in Cells in Excel?

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.


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, "")
        xCell.Value = xStr
   Application.EnableEvents = True
End Sub

Step 3

Then, in the VBA application, click on tools, select references, select the check box named "Microsoft VBScript regular expression," and click OK.

Tools > references > checkbox > ok

Step 4

Then save the workbook as a macro-enabled workbook and click F5 to run the code, and our result will be similar to the below image.


In this tutorial, we used a simple example to demonstrate how you can convert HTML to normal text in Excel.

Updated on: 06-Mar-2023

1K+ Views

Kickstart Your Career

Get certified by completing the course

Get Started