Save HTML as simple text in Excel cell?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

How can I save a string written as HTML format into Excel cell so that it
shows the content without these HTML tags?

Thanks for your help!

Weide
 
If you are using code to save the string, you could insert the following
code after that line:

ActiveSheet.Hyperlinks.Delete

Or, use this code in the ThisWorkbook Module of the workbook:

Private Sub Workbook_SheetChange(ByVal Sh As Object, _
ByVal Target As Excel.Range)
With Target.Hyperlinks
If .Count Then .Delete
End With
End Sub

Manually, right click the link, choose Remove Hyperlink. Only works on one
link at a time.

Or, to completely stop Excel from recognizing all links:
Tools, Options, Spelling Tab, Click on AutoCorrect Options button, remove
the checkmark from "Internet and network paths with hyperlinks".

Regards,

Alan
 
Hi Alan,
Maybe my question is not clear enough. The string containing html tag is
like the following:

"<b>January</b> 14, <b>2000</b> Rebuffed Internet extortionist posts stolen
<b>credit</b> card data <br> <b>...</b> Hacker targets <b>CD Universe's
credit</b> card data (Computerworld) <b>...</b>"

When I save the string, it just shows the whole content letter by letter.
But I am interested to know whether there is a way to let excel cell to
interpret the html string and displays it in a proper way.

Thanks a lot.

Weide
 
Hi, thanks a lot for providing this tool. But i need it to transform all the
selected cells not only the (1,1) cell. I dont know how to program in VBA,
could you help me on that?

Thanks a lot.

Weide
 
Replace the code in module1 with the following code. Clicking the
button will process the cells that you have selected. Note that it
overwrites the original content of the cell.

Peter

Option Explicit

Sub test()
Dim strText As String
Dim rngCell As Range
Dim lCalcMode As Long

With Application
.ScreenUpdating = False
lCalcMode = .Calculation
.Calculation = xlCalculationManual
End With

For Each rngCell In Selection

With rngCell
strText = .Value
'only process a cell that contains ">"
If InStr(strText, ">") > 0 Then
.ClearContents
WriteFormattedText rngCell, strText
.WrapText = True
End If
End With
Next

Application.Calculation = lCalcMode
End Sub
 
I took your string and pasted it into Notepad. I then added some data:

<html>

<body>

<b>January</b> 14, <b>2000</b> Rebuffed Internet extortionist posts stolen
<b>credit</b> card data <br>
<b>...</b> Hacker targets <b>CD Universe's credit</b> card data
(Computerworld) <b>...</b>

</body>

</html>

I saved it to have a name with a .htm extension. I then opened that file in
excel in the normal way (file=>Open) and all the tags were interpreted.
 
Back
Top