Excel html to plain text

How to Convert Hyperlink to Plain Text in Excel?

Have you ever tried to convert a hyperlink to plain text in Excel? If you are facing any difficulty, this article will help you complete the task. The HYPERLINK function creates a shortcut that jumps to another location in the current workbook or opens a document stored on a network server, an intranet, or the Internet. When you click a cell that contains a HYPERLINK function, Excel jumps to the location listed or opens the document you specified.

If we try to convert the hyperlink to plain text in Excel using the direct right-click menu, then it can be a boring and old process. And also, if the hyperlinks are spread across the sheet at different locations, i.e., not in the form of a list, then it can be a time-consuming process to complete our task. So, we can make it happen in a quicker way using the VBA application. In this tutorial, we will learn how to convert hyperlinks to plain text in Excel.

Convert Hyperlink to Plain Text in Excel

Here, we will first create a VBA module and then run it to complete the task. Let’s go over a simple procedure for converting hyperlinks to plain text in Excel using the VBA application.

Step 1

Consider the following image of an excel sheet with hyperlinks in the data.

Now right-click on the sheet name and select view code to open the VBA application, then click on insert and select module.

Right click > view code > insert > module

Step 2

Then type the below-mentioned programme in the text box as shown in the below image.

Program

Sub DeleteHyperlink() 'Updated By Nirmal Cells.Hyperlinks.Delete End Sub

Step 3

Then save the sheet as a macro-enabled workbook and click F5 to run the module. All the hyperlinks in the sheet will be converted to plain text, and our final output will be similar to the below image.

Conclusion

In this tutorial, we used a simple example to demonstrate how you can convert hyperlinks to plain text in Excel.

Источник

How to Convert HTML to Text in Cells in Excel?

Have you ever attempted to convert HTML text to plain text in Excel? If we try to complete this manually, then it can be a time-consuming and inaccurate process. The difference between the html text and normal text is that the html text has a header and footer, which are represented inside «< >«. If those texts are removed from the HTML, they will be treated as plain text. In this article, we will learn how to convert HTML to text in Excel cells. In this tutorial, we will complete this task using the VBA application.

Convert HTML to Text in Cells

Here we will first create a VBA module, enable the references, and run the code to complete the process. Let’s take a look at a simple procedure for converting html to text in Excel cells.

Step 1

Let us consider an Excel sheet where the data is a list of HTML, similar to the below image.

Now right-click on the sheet name and select view code to open the VBA application, then click on insert and select module.

Right click > view code > insert > module

Step 2

Then type the below-mentioned programme into the text box as shown in the below image.

Program

Sub RemoveHTMLTags() 'Updated By Nirmal Dim xRg As Range Dim xCell As Range Dim xStr As String Dim xRegEx As RegExp Dim xMatch As Match Dim xMatches As MatchCollection Set xRegEx = New RegExp Application.EnableEvents = False Set xRg = Cells.SpecialCells(xlCellTypeConstants) With xRegEx .Global = True .Pattern = "<(""[^""]*""|'[^']*'|[^'"">])*>" End With For Each xCell In xRg xStr = xCell.Value Set xMatches = xRegEx.Execute(xCell.Text) For Each xMatch In xMatches xStr = Replace(xStr, xMatch.Value, "") Next xCell.Value = xStr Next Application.EnableEvents = True End Sub

Step 3

Then, in the VBA application, click on tools, select references, select the check box named «Microsoft VBScript regular expression,» and click OK.

Tools > references > checkbox > ok

Step 4

Then save the workbook as a macro-enabled workbook and click F5 to run the code, and our result will be similar to the below image.

Conclusion

In this tutorial, we used a simple example to demonstrate how you can convert HTML to normal text in Excel.

Источник

How to Convert HTML to Text in Cells in Excel

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.

Table of Contents

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.

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.

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.

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.

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.

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.

3. Lastly, we will leave the Replace with empty or blank.

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

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.

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 .

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 .

2. In the VBA window, select Insert .

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

For Each Cell In Selection

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

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

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

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.

Источник