Importing web data into Excel often leaves analysts struggling with unreadable HTML entities like & or ". While standard funding sources typically budget for expensive external ETL tools to clean this data, smaller teams need efficient, native workarounds. Fortunately, utilizing built-in Excel formulas grants you immediate, automated decoding capabilities at zero additional cost. As a stipulation, note that methods like FILTERXML require specific Windows configurations, whereas nested SUBSTITUTE chains offer cross-platform stability. For example, using =FILTERXML("<t>"&A1&"</t>", "//t") quickly converts basic entities. Below, we outline the exact formulas, limitations, and step-by-step conversion workflows.

Excel Formula To Convert Text With HTML Entities

When you export data from web applications, scrape websites, or pull information from APIs into Microsoft Excel, you often encounter messy text filled with HTML entities. Common web symbols turn into cryptic codes: & represents an ampersand (&), < and > stand for less-than and greater-than signs, and numeric codes like ' represent apostrophes.

These entities make your data hard to read, break standard text searches, and ruin your reporting. While Excel does not feature a single, built-in DECODEHTML function, you can solve this problem using several different techniques. Depending on your Excel version and data volume, you can use nested formulas, modern Power Query workflows, XML parsing hacks, or custom VBA scripts.

Method 1: The Nested SUBSTITUTE Formula (Best for Simple, Common Entities)

If your data only contains a handful of standard HTML entities, the most straightforward approach is to nest multiple SUBSTITUTE functions inside one another. This method requires no macros, works on all versions of Excel (including Excel Online and Excel for Mac), and calculates instantly.

The standard syntax for a nested substitute formula targeting the five most common XML/HTML entities is:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2, "&amp;", "&"), "&lt;", "<"), "&gt;", ">"), "&quot;", """"), "&#39;", "'")

How It Works:

• SUBSTITUTE(A2, "&", "&") finds every occurrence of & in cell A2 and replaces it with a normal ampersand.
• The next outer SUBSTITUTE takes that cleaned result and replaces < with <.
• The process repeats sequentially for >, double quotes (represented by """" in Excel formulas), and the numeric apostrophe (').

Limitations:

While safe and highly compatible, this method becomes unmanageable if your text contains dozens of different named or numerical entities (like  , ©, or é). Excel formulas are limited in character length, and nesting more than 10–15 functions makes your spreadsheet difficult to audit and maintain.

Method 2: The FILTERXML Hack (Best for standard XML entities in Windows Excel)

If you are running Excel 2013 or later on Windows, you can leverage the FILTERXML function. This function is designed to parse XML documents. Since XML natively decodes standard entities, we can wrap our text in basic XML tags and let Excel's parsing engine do the heavy lifting automatically.

To decode standard entities in cell A2, use this formula:

=FILTERXML("<t>" & A2 & "</t>", "//t")

Why It Works:

By prepending <t> and appending </t> to your text, you create a valid, single-node XML string. The FILTERXML function reads this string, interprets the nodes, and automatically decodes standard XML entities (like &, <, >, ', and ") when it returns the inner text value.

Crucial Warning:

This trick will return a #VALUE! error if your text contains an unescaped raw ampersand (&) that is not part of a valid entity. If your source text is a mix of already-decoded and encoded characters, wrap the formula with an error handler or stick to the nested SUBSTITUTE method.

Method 3: Power Query (The Ultimate Modern & Bulk Solution)

If you are processing large datasets, importing CSVs, or setting up automated data pipelines, Power Query is the most robust tool for the job. Power Query features a native web-decoding engine that handles all HTML5 entities flawlessly.

To clean your text using Power Query, follow these steps:

1. Select your data range, navigate 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., "Cleaned Text").
4. In the custom column formula box, enter the following Power Query formula:

   Html.Table([OriginalColumnName], {{"Column1", ":root"}}){0}[Column1]

   Note: Replace [OriginalColumnName] with the actual name of your column containing the HTML entities.
5. Click OK. Power Query will generate a new column where every single HTML entity is perfectly decoded.
6. Go to the Home tab, click Close & Load, and return your clean data back to a new Excel worksheet.

This approach handles complex characters, foreign language accents, currency symbols, and web-specific tags without breaking or requiring complex mathematical logic.

Method 4: VBA Custom Function (Best for Dynamic & Offline Macros)

For users who want a custom, reusable formula like =DecodeHTML(A2) directly inside their grid, a short Visual Basic for Applications (VBA) macro is the perfect solution. By using the Windows HTML Document Object Model, we can parse and decode strings instantly using the browser's native capabilities.

To add this function to your workbook:

1. Press Alt + F11 to open the VBA Editor.
2. Click Insert > Module from the top menu.
3. Paste the following code into the module window:

Function DecodeHTML(ByVal htmlText As String) As String
    Dim htmlDoc As Object
    On Error GoTo CleanUp
    
    ' Create an in-memory HTML document
    Set htmlDoc = CreateObject("htmlfile")
    htmlDoc.Open
    
    ' Write the encoded text to the document body
    htmlDoc.write htmlText
    
    ' Retrieve the decoded inner text
    DecodeHTML = htmlDoc.body.innerText
    htmlDoc.Close
    
CleanUp:
    Set htmlDoc = Nothing
End Function

4. Close the VBA Editor and return to your Excel sheet.
5. Save your workbook as an Excel Macro-Enabled Workbook (.xlsm).

Now, you can use your custom function anywhere in your workbook just like a native Excel formula:

=DecodeHTML(A2)

This macro effortlessly handles both named entities (such as © to ©) and numerical entities (such as £ to £).

Summary: Choosing the Right Tool

To help you select the best method for your specific project, consult this quick comparison table:

By applying the right technique above, you can turn unreadable, web-scraped strings back into clean, professional, and searchable datasets inside Microsoft Excel.