Convert HTML in Excel Column to Plain Text

Hi,
This is my first time posting to the forum. I've searched many threads here an have not found the answer I need.

I am trying to build a spreadsheet based on data extracted from my SQL Server.

What I have is a spreadsheet that is linked to a SQL View. The view contains several columns which are straight forward and not formatted when I pull the data into Excel (i.e. Ticket #, User Name, Date Created, etc.). However, the important column that contains the details of the support case is HTML formatted. So the data in that column appears something like this:

"<div>Jim</div>
<div>there were double receipts*in GP on*8/6</div>
<div>I need to get them reversed.</div></div>"

What I would like to see is:

"Jim there were double receipts in GP on 8/6 I need to get them reversed."

I just need that one column converted. I've tried everything I could think of and similar suggestions I've searched, but no luck.

I can send more detail and examples if necessary.

I appreciate any help provided.

JL

I needed something similar a little while ago and stumbled across this on a google group.

All credit goes to Rick Rothstein.

Orignal post can be found http://groups.google.com/group/micro...b8e6462c11746c


In the VBA editor, add a UserForm to your project and put a TextBox and a
CommandButton on it. Make the UserForm somewhat large in order to house a
large TextBox and then make your TextBox large to fill all but enough room
for the CommandButton. For the TextBox in the Properties window, set the
MultiLine property to True and set the ScrollBars property to
3-fmScrollBarsBoth. Now, copy/paste the following code into the UserForm's
code window...


'*********Start UserForm Code*********

Please Login or Register  to view this content.

'*********End UserForm Code*********


Now we need some way to start this all off. For my test condition, I used
the BeforeDoubleClick event of the Worksheet where your HTML text is going
to be located, but you can change this to a button on the Toolbar or some
other mechanism if you want. In the VBA editor, bring up the code window for
the Worksheet you have your HTML text on and copy/paste the following into
it...


'*********Start Worksheet Code*********

Please Login or Register  to view this content.

'*********End Worksheet Code*********


Now, simply double-click a cell with your HTML text in it and read the
parsed content in the TextBox. When you are finished, click the
CommandButton to dismiss the UserForm.

Thanks jj72uk.
This worked great!
However, I'd like to get the text inline and not have to click on the HTML and deal with the user form. Any way to do that?

Jim

shg,
Unfortunately I am really not familiar with VBA. I don't know how to implement this code.
Thanks.
Jim

That is EXACTLY what I needed! Thank you SO much!
Jim

No problem, glad me and shg could help

Hi,
I used this macro also and it does the job great but I want to leave the html tag <CTRL> in the text.
Can I exclude this?