Cleaning database exports in Excel often leaves analysts frustrated with messy HTML tags cluttering their text. While manual Find-and-Replace or complex VBA macros are traditional remedies, they are often destructive or cumbersome to maintain. Fortunately, leveraging dynamic Excel formulas grants you instantaneous, non-destructive data cleansing. As a stipulation, while formulas easily strip basic formatting tags-such as converting <b>Approved</b> to plain "Approved"-complex nested scripts may require Power Query. Below, we outline the exact formula frameworks to automate this purification process.

Excel Formula to Replace HTML Tags with Plain Text

When you export data from content management systems (like WordPress, Shopify, or Magento), database dumps, or web scraping tools, you often end up with cells cluttered with HTML tags. Text like <strong>Special Offer</strong> <p>Save 20% today!</p> is difficult to read and impossible to use in professional reports.

Cleaning this data manually is tedious. Fortunately, Excel offers several powerful methods to strip HTML tags and convert raw code into clean, readable plain text. Depending on your version of Excel and your technical comfort level, you can use modern formulas, Power Query, VBA, or even built-in interactive tools.

Method 1: The Modern Excel Way (REGEXREPLACE)

If you are using Microsoft 365 or Excel for the Web, Microsoft has introduced native Regular Expression (Regex) functions. This is by far the cleanest, most efficient way to remove HTML tags using a formula.

The Formula

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

How It Works

• A2: The cell containing the messy HTML text.
• "<[^>]+>": This is the regular expression pattern.
  • < matches the opening bracket of an HTML tag.
  • [^>]+ matches one or more characters that are not a closing bracket.
  • > matches the closing bracket.
• "": Replaces any matching HTML tags with nothing (an empty string), effectively deleting them.

Example:

Method 2: The Power Query Solution (Best for Large Datasets)

If you have thousands of rows of data, using formulas can slow down your workbook. Power Query (built into Excel 2016 and newer) handles data cleaning externally and is incredibly robust. It even has a built-in engine to parse HTML.

Step-by-Step Guide:

1. Select your data range, go to the Data tab, and click From Sheet (or From Table/Range).
2. Once the Power Query Editor opens, go to the Add Column tab and select Custom Column.
3. Name your new column (e.g., CleanText) and enter the following Power Query formula:

   Html.Table([YourColumnName], {{"Text", "body", each _}}){0}[Text]

   (Replace [YourColumnName] with the actual name of your column containing HTML).
4. Click OK. Power Query will parse the HTML and extract only the plain text body.
5. Go to the Home tab, click Close & Load, and your clean data will load back into a new Excel sheet.

Method 3: VBA User-Defined Function (For Older Excel Versions)

If you are using Excel 2019, 2016, or older, you won't have access to the REGEXREPLACE function. However, you can easily create your own custom formula using a small snippet of VBA code.

Step-by-Step Guide:

1. Press ALT + F11 to open the Visual Basic for Applications (VBA) editor.
2. Click Insert > Module.
3. Copy and paste the following code into the empty module window:

Function StripHTML(Cell As Range) As String
    Dim RegEx As Object
    Set RegEx = CreateObject("VBScript.RegExp")
    
    With RegEx
        .Pattern = "<[^>]+>"
        .IgnoreCase = True
        .Global = True
    End With
    
    If Not IsNull(Cell.Value) Then
        StripHTML = RegEx.Replace(Cell.Value, "")
    Else
        StripHTML = ""
    End If
End Function

4. Close the VBA window and return to your Excel workbook.
5. You can now use your custom formula just like any native Excel function:

   =StripHTML(A2)

Method 4: Quick & Dirty Find and Replace (No Formulas)

If you just need a quick, one-time fix and don't want to write formulas or code, you can use Excel's built-in Find and Replace wildcard feature.

Step-by-Step Guide:

1. Select the column containing the HTML text.
2. Press CTRL + H to open the Find and Replace dialog box.
3. In the Find what box, type: <*>
4. Leave the Replace with box completely empty.
5. Click Replace All.

Warning/Limitations:

While this method is incredibly fast, it uses the asterisk (*) as a wildcard. This means it finds an opening bracket <, matches absolutely everything it can, and stops at the closing bracket >. If your cell contains mathematical expressions like "If A < B and C > D", Excel will delete everything between those two symbols, destroying your actual text. Use this method with caution!

Method 5: Flash Fill (The AI-Assisted Way)

Excel's Flash Fill feature acts like a smart assistant. By providing a few examples of what your clean data should look like, Excel can automatically figure out the pattern and clean the rest of the column.

Step-by-Step Guide:

1. Insert a blank column next to your HTML column.
2. In the first row of the new column, manually type out the clean, plain text version of the HTML cell next to it.
3. Move to the next row down and start typing the clean version of that row. Excel will likely show a grayed-out preview of the cleaned column.
4. If the preview looks correct, press Enter to accept it.
5. If the preview doesn't appear, select the entire range of your empty column and press CTRL + E (or go to the Data tab and click Flash Fill).

Summary: Which Method Should You Use?

Final Pro-Tip: Dealing with HTML Entities

Once you strip HTML tags, you might still be left with HTML character entities like & (which represents "&"), " ("), or   (non-breaking space).

To clean these up, you can nest your formulas inside a series of SUBSTITUTE functions. For example:

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

This ensures your final export is truly plain, print-ready, and reader-friendly text!