Removing HTML code from Excel cell

[sarayewo]

I have a report that comes out of one of our web-based systems in which I that contains user input. Whenever I get the report this particular cell contains the HTML code that was used to format its contents in the web system.

I need a code that I can execute to remove the HTML code and only leave the actual content... Would this be possible?

I tried simply using Find/Replace and replacing "<*>" with blanks, but I get an error message "Formula too long"... Any ideas?

 

[apgp88]

Sometimes text is replaced by hashes.
Can you please tell , how to avoid that ?

 

[kendra_dbpros]

This is excellent, but when I run this in Excel 2007, it is replacing the text with XXXXXX for all data it fixes. I can see the values when I am in a specific cell, but when copy/paste it elsewhere, it's just XXXXX. How can I fix this?

You could just wrap the preceding code in the Replace VBA function and specify it to replace your chosen characters:

Sub RemoveTags()
Dim r As Range
Selection.NumberFormat = "@"  'set cells to text numberformat
With CreateObject("vbscript.regexp")
  .Pattern = "\<.*?\>"
  .Global = True
  For Each r In Selection
    r.Value = Replace(.Replace(r.Value, ""),"&nbsp"," ")
  Next r
End With
End Sub

The amendment is in Red.

 

[hemmi1]

Richard Schollar has made a huge contribution to my career

now, I'd like to see what other goodies that "CreateObject" can do -- is it calling a library in Vb, Vbscript?
Thanks a lot !! I had NO problems whatever with this routine, and then I replace all "nbsp;" and then I'm good to go.

 

[sclark3]

I found this thread really useful to sort out several tags in my Excel that I wanted removing. I have modified the original replace statement nesting several times for various & tags.

I have one final hurdle I just can't seem to get over because I don't know VBA really (I don't even get how that .pattern works), so am relying on these type of forums to piece it together. I don't really understand what the difference is between the replace and the .replace in the nested statements and don't know what order they process in.

My final problem that I want to solve is how to replace the <br> tag with ALT+Enter to put a new line. The original code works fine for getting rid of all the tags in one go, but before I get rid of them all I want to replace just the line breaks and insert and actual new line, then continue to remove the rest of the HTML tags and the & tags.

My replace looks like this (it's within a sub that uses different var names). I'm actually replacing the codes for & less than ; etc. but the preview shows them as < for the search string and the replacement string (I'm sure you get the idea).

With CreateObject("vbscript.regexp")
  .Pattern = "\<.*?\>"
  .Global = True
  For Each DataRange In Selection
    DataRange.Value = Replace(Replace(Replace(Replace(Replace(.Replace(DataRange.Value, " "),"<br>", vbNewLine), "&amp", "&"), "<", "<"), ">", ">"), "@", "@")
'    DataRange.Value = Replace(Replace(Replace(Replace(.Replace(Replace("<br>", vbNewLine),DataRange.Value, " "), "&amp", "&"), "<", "<"), ">", ">"), "@", "@")  Next DataRange
End With

The commented out replace line didn't work, I was trying to see if swapping round the order of the original .replace " " with the replace <br> next to it would work but it didn't.

Please help me sort this out.
Thanks.

You could just wrap the preceding code in the Replace VBA function and specify it to replace your chosen characters:

Sub RemoveTags()
Dim r As Range
Selection.NumberFormat = "@"  'set cells to text numberformat
With CreateObject("vbscript.regexp")
  .Pattern = "\<.*?\>"
  .Global = True
  For Each r In Selection
    r.Value = Replace(.Replace(r.Value, ""),"&nbsp"," ")
  Next r
End With
End Sub

The amendment is in Red.