Data Import & Export

How to Replace the HTML Ampersand Entity in Excel Data

Huda Qurayshi Sep 28, 2026 872 views

Question details

The user needs to convert the HTML-encoded ampersand entity (&) back into a standard ampersand (&) character within an Excel spreadsheet.

How to Replace the HTML Ampersand Entity in Excel

Product: Excel
Device & OS: not provided
Scenario: Importing or retrieving data from external web sources or databases where special characters are HTML-encoded.
Observed behavior: Excel displays the literal string '&' instead of the standard '&' symbol in the worksheet cells.

Before you start

Highlight only the specific columns containing the imported web data to ensure you do not inadvertently replace necessary code snippets or URLs elsewhere in the worksheet during a mass replacement.

Solution 1Recommended

Use Excel's Find and Replace Feature

The quickest and most direct way to fix HTML entities across your entire worksheet is by using the built-in Find and Replace tool.

This method is ideal for one-off data imports where you need an immediate fix without setting up complex queries.

Select the Target Range

Highlight the specific cells, rows, or columns containing the '&' text. If you want to search the entire sheet, simply click any single empty cell.

Open Find and Replace

Press 'Ctrl + H' on your keyboard, or navigate to the 'Home' tab and click 'Find & Select' > 'Replace' to open the dialog box.

Enter the Search Terms

In the 'Find what' field, type '&'. In the 'Replace with' field, type a single ampersand symbol '&'.

Execute the Replacement

Click 'Replace All' to instantly update all instances of the HTML entity in your selected range.

Verification Popup: Excel will display a popup showing exactly how many replacements were made. Verify this number aligns with the size of your imported dataset.

Solution 2

Decode HTML Entities in Power Query Before Loading

If you frequently import data that contains HTML entities, you can clean the text automatically during the import process using Power Query.

Solution 3

Use the SUBSTITUTE Formula

Use a formula to create a cleaned version of your data in a separate column without altering the original raw data.

Data Cleaning Made Easy

Fix HTML Entities Easily with WPS Spreadsheet

WPS Spreadsheet provides a robust and user-friendly Find and Replace tool, making it incredibly simple to clean up imported web data and fix HTML formatting issues in seconds.

1. Open your File: Launch WPS Spreadsheet and open the file containing your imported external data.
2. Launch Find & Replace: Press 'Ctrl + H' to immediately bring up the Find and Replace window.
3. Set the Parameters: Enter '&' in the 'Find what' box and the standard '&' symbol in the 'Replace with' box.
4. Clean the Data: Click 'Replace All' to effortlessly correct all HTML entities across your document.

Fully compatible with Microsoft Excel (.xlsx, .xls, .csv) formats.Lightning-fast Find and Replace functionality for rapid data cleaning.Advanced data import features to handle external text safely.Lightweight, ad-free interface that loads large datasets quickly.

microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel show & instead of a standard ampersand?

This occurs when you import data from a web page or an external database where special characters are HTML-encoded for safe transmission. Excel reads the literal encoded text rather than decoding it automatically.

Can I replace multiple different HTML entities at once?

Find and Replace handles one string at a time. If you have multiple entities like " (quotes) or < (less than), you will need to run a separate Find and Replace action for each, or use Power Query to set up multiple replacement steps during import.

Will replacing & break any URLs in my spreadsheet?

It might. URLs often use standard ampersands (&) to separate parameters, but if they were encoded as &, replacing them will change the URL string. Always highlight the specific data column you want to clean rather than replacing across the entire sheet to protect valid URLs.

Excel & Data Problems