How to Remove HTML Tags from Text in Excel

Cleaning raw HTML tags from web-scraped text in Excel is notoriously tedious, often stalling critical data analysis. While enterprise data budgets typically fund expensive third-party cleansing platforms, smaller operations require more accessible, immediate alternatives.

Implementing a native Excel formula grants immediate, zero-cost data refinement without software overhead. As a stipulation, note that native formulas excel with standard tags like paragraph markers, whereas highly complex nested scripts require Power Query. For example, nesting SUBSTITUTE functions successfully purges <b> tags from raw scraped descriptions. Below, we outline the exact formula syntax to streamline your data workflow.

Web scraping is an incredibly powerful way to gather data from the internet, but the raw output is rarely ready for analysis. More often than not, when you scrape text from a website, you end up with a messy mix of actual content and raw HTML markup-such as <p>, <div>, <a href="...">, and <span> tags.

While you could clean this data using Python or external tools before importing it, Excel offers several powerful ways to clean HTML tags directly inside your workbook. Depending on your version of Excel, you can use the brand-new regular expression formulas, nested legacy formulas, Power Query, or custom VBA functions. In this guide, we will walk through the best formulas and techniques to strip HTML tags from your text and leave you with clean, usable data.

Method 1: The Modern Excel 365 REGEXREPLACE Formula (Recommended)

For years, Excel users envied Google Sheets' REGEXREPLACE function. Thankfully, Microsoft has finally introduced native Regular Expression functions to Excel 365. If you are using an updated version of Excel 365, this is by far the easiest, cleanest, and most robust formula-based method to strip HTML tags.

The Formula:

=REGEXREPLACE(A2, "<[^>]+>", "")

How It Works:

• A2: This is the cell containing your raw, web-scraped HTML text.
• "<[^>]+>": This is the regular expression pattern.
  • < matches the opening angle bracket of an HTML tag.
  • [^>]+ matches one or more characters that are not a closing angle bracket (>). This ensures that the formula captures the entire tag, including attributes like class="..." or href="...".
  • > matches the closing angle bracket.
• "" (Empty String): This tells Excel to replace any matched HTML tag with absolutely nothing, effectively deleting it.

This single formula handles everything from simple tags like <b> to complex hyperlinked tags like <a href="https://example.com" class="link"> in one single sweep.

Method 2: Handling HTML Entities (The Cleanup After the Cleanup)

Stripping the bracketed HTML tags is only half the battle. Often, web-scraped text contains encoded HTML entities such as   (non-breaking space), & (ampersand), < (less-than sign), and > (greater-than sign).

To clean these up, you can nest the REGEXREPLACE function within a series of SUBSTITUTE functions. Here is how to construct a comprehensive formula that cleans both the tags and the most common HTML entities:

=SUBSTITUTE(
    SUBSTITUTE(
        SUBSTITUTE(
            REGEXREPLACE(A2, "<[^>]+>", ""), 
            "&nbsp;", " "
        ), 
        "&amp;", "&"
    ), 
    "&quot;", """"
)

This nested formula first strips the structural HTML tags, then replaces non-breaking spaces with standard spaces, converts & to standard ampersands, and restores double quotes from their HTML entity format.

Method 3: The Legacy Excel Formula Workaround (No Office 365/Regex)

If you are working on an older version of Excel (Excel 2016, 2019, or 2021) that does not support regular expressions, you cannot easily parse arbitrary HTML using standard formulas. However, if your scraped text only contains a few known, repetitive tags (like paragraph and bold tags), you can use a chain of nested SUBSTITUTE functions.

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2, "<p>", ""), "</p>", ""), "<b>", ""), "</b>", "")

Limitations: While this works for predictable formatting, it quickly becomes unmanageable if your HTML contains dynamic attributes (like varying URLs in anchor tags) because SUBSTITUTE requires an exact match to work.

Method 4: Using Power Query's Html.Table (The Non-Formula "Formula" Solution)

If you have complex HTML or large datasets, trying to clean tags with standard worksheet formulas can slow down your workbook. Excel's Power Query engine features a highly advanced, built-in HTML parser that can extract text instantly without complex regex patterns.

You can use the Html.Table M-code formula inside Power Query to let Excel treat your scraped text like a true web document.

Step-by-Step Guide:

1. Select your data range containing the raw HTML.
2. Go to the Data tab and click From Table/Range to open the Power Query Editor.
3. Go to the Add Column tab and click Custom Column.
4. Name your column (e.g., CleanText) and enter the following Power Query formula:

   Html.Table([YourHTMLColumnName], {{"extracted_text", ":root"}})

5. Click OK. You will see a new column containing a Table object.
6. Click the expand icon (two arrows pointing outwards) next to the new column header, uncheck "Use original column name as prefix", and click OK.
7. Go to the Home tab, click Close & Load, and your clean text will be returned to a new worksheet.

This is arguably the most professional way to clean scraped text because Power Query automatically decodes all HTML entities and strips all structural tags behind the scenes, using the actual HTML Document Object Model (DOM).

Method 5: VBA Custom User-Defined Function (UDF)

If you don't have Excel 365 (meaning no REGEXREPLACE) and you want a simple formula you can use directly on your worksheet without loading Power Query, you can create a custom VBA function. This function uses Windows' built-in VBScript Regular Expressions library.

The VBA Code:

Function CleanHTML(ByVal htmlText As String) As String
    Dim regEx As Object
    Set regEx = CreateObject("VBScript.RegExp")
    
    With regEx
        .Pattern = "<[^>]+>"
        .Global = True
        .IgnoreCase = True
    End With
    
    ' Strip HTML Tags
    Dim cleanStr As String
    cleanStr = regEx.Replace(htmlText, "")
    
    ' Clean up common HTML entities
    cleanStr = Replace(cleanStr, "&nbsp;", " ")
    cleanStr = Replace(cleanStr, "&amp;", "&")
    cleanStr = Replace(cleanStr, "&quot;", """")
    cleanStr = Replace(cleanStr, "&lt;", "<")
    cleanStr = Replace(cleanStr, "&gt;", ">")
    
    CleanHTML = Application.WorksheetFunction.Trim(cleanStr)
End Function

How to Use It:

1. Press Alt + F11 to open the VBA Editor.
2. Click Insert > Module.
3. Paste the code above into the module window.
4. Close the VBA Editor and return to your Excel worksheet.
5. Now, you can use your custom formula just like any native Excel formula:

   =CleanHTML(A2)

Choosing the Right Tool for the Job

Which method you should choose depends entirely on your environment and the complexity of your scraped data:

Summary

Cleaning raw HTML from web-scraped data no longer requires switching back and forth between Excel and text editors. If you are fortunate enough to be running the latest version of Excel 365, the REGEXREPLACE formula solves the problem in a single line. For older versions, leveraging Power Query's structural HTML engine or importing a quick VBA user-defined function will keep your data pipelines clean, automated, and ready for visualization.