Html to text миф

Html to text миф

Issue: I want to be able to grab text text from an .html file (with all of it’s tags) and have it converted to normal, readable text (without the tags) like it would appear in a browser. I’m currently using the following:

Function HtmlToText(sHTML) As String Dim oDoc As HTMLDocument Set oDoc = New HTMLDocument oDoc.body.innerHTML = sHTML HtmlToText = oDoc.body.innerText End Function

I’m passing in a string which includes all of the tags, and I am getting a normal text string back out. The issue is that it is removing all of the formatting. Changes in em size, bold tags, italic tags, and underline tags are all removed and the text is just rendered plain. Is there any way to take text from an html file and get it pasted into word so that it looks the same as it looks in the browser (i.e. retaining font size changes, bold, italic, underline, etc.)? If I can get this to work, it will save hundreds of hours of work. Your help is truly appreciated.

Additional context for those that want it: We have folders with thousands of html files in them. Some of these files (several dozen) are selected to be used and the file names are marked in a spreadsheet. The content of the selected files then needs to be put into a single word document with an informational header placed above the content from each html file. This was an entirely manual process — someone would go to the folder, find each individual file, open it, copy the relevant text, type in the informational header, and then paste the text from the html file into the word doc. This was being constantly done over and over, so I wanted to see if we could automate the process. Once the files were selected and recorded in the excel doc, I wanted Word VBA to build the final doc by using the information that was in excel. The good news is that I’m 99% there. I just really need some help with the last part. So far, I can select the excel file from word, and word will create all the headers, find the correct files, get the html text, convert it to plain text, and insert it into Word. I just need it to retain the html formatting when it gets copied over.

Источник

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.

Sub RemoveHTMLTags() 'Update by Extendoffice 20180703 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