How do I get HTML codes in an Excel Spreadsheet to display or conv

S

scottydm3

Hi folks, Thanks for taking the time to look and help out!

I have data coming from an SQL db and some of the fields of my report have
been formatted via VBA to do things like convert email strings into actual
email links, and some of the data is just plain text.

My problem is that one of the fields has imbedded HTML codes that were
created by the original app and stored within that field in the SQL db. Only
some of the records have the formatting but when they do, it is typically for
text formatting, but occassionally to imbed links. I need to have Excel
interpret these HTML codes in this one field back to the text formatting and
links that they represent.

Here is an example:
<html><body>Folder to contain requirments <b>rejected</b> because they are
duplicates.</body></html>

How to I get Excel 2003 (or 2007 if necessary) to convert the HTML?
If it can be "converted", can Excel interpret or view the HTML? If not, can
it be stripped out?


Thanks in advance for your ideas!
 
G

GSnyder

I don't know of any kind of translator, but a little bit of VB code could
help you strip out the HTML code. Try inserting this into a module (change
the range ("D21:D50") and sheet name ("Sheet1") to what you need, of course).
It can also be made more flexible to be a function to handle whatever range
you want.

Sub RemoveHTML()
Dim rngCell As Range
Dim strInput, strOutput As String
Dim i As Integer
Dim bSkipCapture As Boolean

Sheets("Sheet1").Select
For Each rngCell In Range("D22:D50")
If Len(rngCell) > 0 Then
strInput = rngCell.Value
For i = 1 To Len(strInput)
If Mid(strInput, i, 1) = "<" Then bSkipCapture = True
If Not bSkipCapture Then
strOutput = strOutput + Mid(strInput, i, 1)
End If
If Mid(strInput, i, 1) = ">" Then bSkipCapture = False
Next
rngCell = strOutput
strOutput = ""
End If
Next
End Sub
 
S

scottydm3

Well, that's pretty straight forward. This worked! Thank you VERY MUCH for
your help!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Pasting HTML script to Excel 2007 converts the cells format to 'General' 0
Outlook 2016 Add-in that creates an html signature doesn't embed images(logos). How can I do this ? 0
Replace html code - formula too long 1
I have an email with html content. How do I read this? 1
How do I convert shading to highlighting for specific colors 8
How to embed an image into an email or HTML file? 1
How do I insert a file into Excel 2007 spreadsheet ? 1
Possible to save Excel as CSV with HTML codes? 2

Top