HTML tags are pretty common when working with raw data. Generally, when we copy data from somewhere else, the code inside the data gets copied too. But, as users, we only need the main texts without the HTML tags. So, in this article, I will show you how to remove HTML tags from a text in Excel.
2 Suitable Ways to Remove HTML Tags from Text in Excel
Say, you have a dataset of text in an HTML file.
Now, you have copied and pasted the text into Excel. And now, you are getting the text with HTML codes. But you don’t need this.
Now, you can remove these HTML tags from a text in Excel by following the described ways below.
1. Remove HTML Tags Using Find and Replace Tool
The simplest and quickest way to remove HTML tags from Excel is to use the Find and Replace tool. Follow the steps below to accomplish this.
📌 Steps:
- First, select the cells (B5:B9 here) where your texts with HTML tags are present.
- Subsequently, press Ctrl + H on your keyboard.
- As a result, the Find and Replace window will appear.
- Following, go to the Replace tab >> insert <*> in the Find what: text box >> Leave a blank in the Replace with: text box >> click on the Replace All button.
- Consequently, a Microsoft Excel dialogue box will appear showing you the information about replacements.
- Subsequently, click on the OK button.
Thus, you will be able to remove HTML tags from the texts in Excel and the final outcome would look like this.
Read More: How to Remove Drop Down Arrow in Excel
2. Use VBA Code to Remove HTML Tags from Text in Excel
Furthermore, you can use a VBA code to remove HTML tags from texts in Excel. Follow the steps below to do this.
📌 Steps:
- First, go to the Developer tab >> Visual Basic tool.
- As a result, the VB Editor window will open.
- Subsequently, go to the Insert tab here >> click on the Module option.
- As a result, a new module named Module1 will be created.
- Now, go to Module1 >> insert the following code in the code window >> press Ctrl + S on your keyboard.
Sub RemoveHTMLTags()
Dim lookupRange As Range
Dim lookupCell As Range
Dim lookupAddress As String
On Error Resume Next
lookupAddress = Application.ActiveWindow.RangeSelection.Address
Set lookupRange = Application.InputBox("Select your data range", "Remove HTML Tags", lookupAddress, , , , , 8)
Set lookupRange = Application.Intersect(lookupRange, lookupRange.Worksheet.UsedRange)
If lookupRange Is Nothing Then Exit Sub
lookupRange.NumberFormat = "@"
With CreateObject("vbscript.regexp")
.Pattern = "\<.*?\>"
.Global = True
For Each lookupCell In lookupRange
lookupCell.Value = .Replace(lookupCell.Value, "")
Next
End With
End Sub
- Consequently, a Microsoft Excel dialogue box will appear.
- Subsequently, click on the No button.
- As a result, the Save As window will appear.
- Following, choose the Save as type: option as .xlsm file and click on the Save button.
- Now, close the VB Editor.
- Afterward, go to the Developer tab >> Macros tool.
- As a result, the Macro window will appear.
- Following, click on the RemoveHTMLTags macro and click on the Run button.
- Consequently, the Remove HTML Tags window will appear.
- Now, put cell reference B5:B9 in the Select your data range text box and click on the OK button.
As a result, the HTML tags from your texts will be removed and you will get the desired output which should look like this.
Read More: How to Remove Compatibility Mode in Excel
Download Practice Workbook
You can download our practice workbook from here for free.
HTML File:
Excel File:
Conclusion
So, in this article, I have shown you 2 suitable ways to remove HTML tags from text in Excel. You can also download our free workbook to practice. I hope you find this article helpful and informative. If you have any further queries or recommendations, please feel free to comment here.
And, visit ExcelDemy to learn more things about Excel! Have a nice day!
