How to remove all HTML tags from string in Excel?
When dealing with data exported from web pages, content management systems, or HTML forms, your Excel worksheets may contain text strings embedded with HTML tags, such as <div>, <p>, <span>, <a href>, and more. These tags are used to format or mark up content on web pages, but in Excel, they are unnecessary clutter that can obstruct data analysis, prevent sorting and filtering, and interfere with further processing or reporting. Being able to remove all HTML tags from your strings efficiently is essential for cleaning, preparing, and analyzing your data more effectively.
This article introduces several step-by-step solutions to help you remove HTML tags quickly and accurately in Excel. Whether you prefer using built-in Excel features, reliable add-ins like Kutools for Excel, or programmatic approaches, you’ll be able to clean your data for further processing and reporting with minimal effort.
 |
 |
 |
Remove all HTML tags from text string with Find and Replace command
Remove all HTML tags from text string with Kutools AI
Remove all HTML tags from text string with VBA code
Remove all HTML tags from text string with Find and Replace command
One of the simplest methods to remove all HTML tags from your Excel strings is to use the Find and Replace feature. This method is especially suitable when all tags follow the normal format (<tag>), and there are no nested < or > characters except as tags. It's quick, requires no additional add-ins or programming knowledge, and works directly within Excel. However, it is best applied to plain text data, and tags spanning over multiple lines or with embedded < or > in content may not be fully removed with this method.
Follow these steps to remove HTML tags using Find and Replace:
1. Select the cells that contain the HTML tags you want to remove. You can select a single column, a range, or even multiple noncontiguous areas by holding the Ctrl key while selecting.
2. Press Ctrl + H on your keyboard to open the "Find and Replace" dialog box. In the dialog that appears:
- In the Find what box, enter <*>. This uses the asterisk (*) wildcard to match any content enclosed within < and >.
- Leave the Replace with box empty. This means any text matching the pattern will be deleted.
3. Click the Replace All button. Excel will instantly search for all HTML tags matching the pattern in your selected range and remove them all at once.
Notes and tips:
- Wildcards will match the minimum number of characters between < and >. If your text contains extraneous < or > not as tags, they may distort the result.
- This method will completely delete the tags, but leave the content outside of tags untouched.
- If no changes are made, make sure your data does not contain line breaks within tags, as these break the pattern matching.
- Check your results: sometimes HTML entities like
<or>are present as content, and these will not be matched or removed by this method.
This approach is most effective for short, well-formed HTML snippets. For complex or heavily nested HTML structures, consider using advanced solutions like VBA or specialized add-ins.
Remove all HTML tags from text string with Kutools AI
For users looking for a straightforward and automated solution, Kutools for Excel provides a highly convenient way to remove HTML tags using its AI Aide feature. With Kutools AI, you simply describe what you want to achieve in plain language, and the add-in will analyze your request and find a precise solution—no need for manual steps, complex functions, or scripting. This makes the process faster and accessible to users of all technical backgrounds. Kutools is especially recommended for recurring data cleaning tasks or large datasets.
After installing Kutools for Excel, do the following to invoke the AI Aide functionality for this task:
- Select the cells that contain the text strings with unwanted HTML tags.
- In the AI Aide pane, type your request clearly in the chat box. For example:
Remove all HTML tags from selected range - Press Enter, or click the Send button. Kutools AI will process your instruction and, once ready, display a suggested solution. Click Execute to apply the result directly to your selected range.
Advantages: Kutools AI is user-friendly, flexible, and especially suitable if you are not familiar with Excel formulas or coding, or if you regularly handle various types of text cleaning tasks. You can use it for multiple repetitive operations, saving significant time.
Points to consider:
- An active internet connection may be required to use the AI Aide features, depending on your version.
- Always double-check results on sample data before applying to your entire worksheet, especially when working with sensitive information.
Remove all HTML tags from text string with VBA code
If you need a more robust and customizable solution—especially when HTML content is complex or inconsistent—a VBA macro can efficiently strip all HTML tags from your data. This approach gives you greater flexibility and can process complex or lengthy text that might not be handled well by Excel's standard tools.
VBA is ideal when:
- You need to process large datasets regularly or automate the cleaning process.
- HTML tags are nested or inconsistent.
- You wish to integrate custom logic, such as keeping some tags while removing others.
Use the following steps to set up and run the VBA code:
1. Press Alt + F11 in Excel to open the Microsoft Visual Basic for Applications editor.
2. In the VBA editor, click Insert > Module, then paste the provided VBA code into the new module window below.
VBA code: remove all HTML tags from text string
Sub RemoveTags()
'updateby Extendoffice
Dim xRg As Range
Dim xCell As Range
Dim xAddress As String
On Error Resume Next
xAddress = Application.ActiveWindow.RangeSelection.Address
Set xRg = Application.InputBox("please select data range", "Kutools for Excel", xAddress, , , , , 8)
Set xRg = Application.Intersect(xRg, xRg.Worksheet.UsedRange)
If xRg Is Nothing Then Exit Sub
xRg.NumberFormat = "@"
With CreateObject("vbscript.regexp")
.Pattern = "\<.*?\>"
.Global = True
For Each xCell In xRg
xCell.Value = .Replace(xCell.Value, "")
Next
End With
End Sub
3. Press F5 to run the code. When a dialog appears, select the cells from which you want to remove HTML tags. See the example below:
4. Click OK, and the macro will process each cell in your selection, removing all HTML tags in one go.
Troubleshooting tips and suggestions:
- If the code does not run, make sure macros are enabled in your Excel settings.
- It's recommended to back up your workbook before running any VBA code, especially on important data.
- If you encounter an error message, check for merged cells, locked sheets, or special cell formats in your selection.
- Nested or broken HTML tags may require modifications to the pattern used in the VBA code.
- For best performance, select only the relevant data range as large selections may slow down execution.
This VBA method provides a powerful, programmable solution for cleaning HTML-heavy data. Suitable for repeated use and adaptable to many data structures, it's especially useful for more technical users or when dealing with complex real-world text input.
Each of these methods—Find & Replace, Kutools AI, or VBA code—provides an effective solution for removing HTML tags in Excel. The best approach depends on your data complexity and your proficiency with Excel or add-ins. Always backup your data before applying global operations, and test your chosen method on a small range first to ensure accurate results. If you still encounter problems or wish to automate further, explore custom formulas or third-party text processing add-ins that suit your needs.
If you're interested in discovering more Excel data cleaning strategies and timesaving workflows, our website offers thousands of tutorials for Excel users of all skill levels.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in