How to Convert HTML to Text in Cells in Excel

This guide will discuss how to convert HTML to Text in Cells in Excel using two methods.

In other words, there are two ways we can use to convert html to text in cells in Excel, which are using the Find and Replace feature and VBA.

Hypertext markup language, or html, is a standardized formatting system that is used to create web pages. So it is a computer language that is often used for web development, internet navigation, and web documentation.

An html code always contains tags, < and >, which can make it difficult to read, especially when placed in a cell in Excel. So there are two ways to convert html to text in Excel.

One is using the Find and Replace feature. The Find and Replace feature in Excel finds the character you specify and replaces it with any text, character, or number you input. 

Another way is using VBA in Excel. VBA stands for Visual Basic for Applications. It is a programming language in Excel and other Office apps. And VBA automates repetitive tasks, data processing, and generating graphs and reports. 

VBA is useful for converting html to text in the entire worksheet, all while using a VBA code. If we have certain repetitive tasks in Excel, we can utilize VBA and record a macro to automate those repetitive tasks.

But VBA is not available for the web-based version of Excel. We recommend using the VBA method when working with the Microsoft Excel application or the Office 360 version. So only the free version of Excel does not support working with VBA.

Let’s take an example.

Suppose you are a web designer and you want to share how you created a certain web page with your colleagues. But some of them find it difficult to read html. So you need to convert the html to text in the spreadsheet to make it easier to read and share with others.

Awesome! Let’s move on and check out how to convert html to text in Excel using the two methods.



A Real Example of Converting HTML to Text in Cells in Excel

First, let’s focus on an example of how to convert html to text in cells using the Find and Replace feature. For instance, you have an html code in a cell.Sample html code in cell

 

Since html codes always have tags, < and >, we simply need to find those tags in the cells. Then, we can replace it with nothing or an empty string. So this method will simply remove the tags in the html, and we will be left with just the text. 

And this is what it will look like after removing the tags. So we have converted html to text.Converted html to text

 

Then, let’s see an example of converting html to text using VBA. Essentially, it does the same thing as the Find and Replace feature. But it utilizes a programming code instead. 

VBA allows us to convert html to text in the entire worksheet. This is what the VBA window in Excel looks like. Microsoft Visual Basic for Applications window

 

We will convert html to text by copying a macro code in the module window. After all the tags, < and >, are removed from the entire worksheet, we will only be left with the text. 

You can make your own copy of the spreadsheet above using the link attached below. 



How to Convert HTML to Text in Cells in Excel Using Find and Replace

This section will explain the step-by-step process of how to convert HTML to Text in cells in Excel using the Find and Replace feature.

1. First, select the cell containing the html you want to convert to text. In this case, we will select A2. Then, press Ctrl + H to open the Find and Replace window.html to text in Cells in Excel

2. Next, input ‘<*>’ in the Find what. Also, the * is a wildcard character that basically tells Excel to look for anything that starts and ends with tags.html to text in Cells in Excel

3. Lastly, we will leave the Replace with empty or blank.html to text in Cells in Excel

4. Since we only selected one cell, simply click Replace. Otherwise, it will apply the function to the whole worksheet.html to text in Cells in Excel

Furthermore, you can click the Replace All option if you have more than one cell selected.

5. And that’s it! You have successfully converted html to text using Find and Replace.html to text in Cells in Excel

6. Additionally, we may end up with a weird format after doing this. For example, the cell may go all the way down in the worksheet. To fix it, simply select the cell. Then, go to Home and select Wrap Text.Fixing formatting error

 

 

How to Convert HTML to Text in Excel Using VBA

This section will focus on the steps in converting html to text in Excel using VBA.

1. First, we need to open the VBA window in Excel. To do this, press Alt + F11.VBA window

2. In the VBA window, select Insert.Converting html to text using VBA

3. Third, click Module. Then, input this code: 

Sub HTML_Removal()

Dim Cell As Range

With CreateObject("vbscript.regexp")

    .Pattern = "\<.*?\>"

    .Global = True

    For Each Cell In Selection

        Cell.Value = .Replace(Cell.Value, "")

    Next

End With

End Sub

4. Next, select the cells containing the html code you want to convert to text.html to text in Cells in Excel

5. Finally, click Run or press the F5 key to run the macro code.Running the macro code

6. And tada! You have converted the html to text in Cells in Excel using a macro code in VBA.html to text in Cells in Excel

 

That’s it! You have successfully learned how to convert html to text in cells in Excel using two methods: the Find and Replace feature and VBA. Now you can convert html to text whether you are using a free version of Excel or the application. 

Are you interested in learning more about what Excel can do? You can now use the various other Microsoft Excel formulas available to create great worksheets that work for you. Make sure to subscribe to our newsletter to be the first to know about the latest guides and tutorials from us.

Get emails from us about Google Sheets.

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'll love what we are working on! Readers receive ✨ early access ✨ to new content.

 

0 Shares:
Leave a Reply

Your email address will not be published.

You May Also Like