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?

 

[Richard Schollar]

Hi

You can try the following script to remove html tages in the selected cells:

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(r.Value, "")
  Next r
End With
End Sub

Open up the VBE with Alt+F11 and locate your workbook in the top left Project Explorer window. Right-click on your workbook and choose Insert>Module. Paste the code in to the code module that will open. Go back to Excel, select the cells you want to cleanse and activate the macro with Tools>Macro>Macros.

 

[sarayewo]

Thanks Richard, that worked like a charm!

One more question: is it possible to set more replacements, or do I have to call a new macro for each option?

For example, aside from HTML tags there are also character markers such as "&nbsp" "&middot" etc.

What I did is copied the code into multiple subs and having the first one call the second one, that one call the third one etc... each clearing a certain tag. Is there an easier way to do this?

 

[Richard Schollar]

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.

 

[vinayak.gouda]

Following code is not working in my machine. It says Sub or Function not defined. Please let me know If I am missing something

Sub Macro1()
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

 

[Richard Schollar]

Hi

Which line does the VBE highlight when it gives you that error message?

 

[vinayak.gouda]

r.Value = Replace(.Replace(r.Value, " "), "&nbsp", ">")

 

[Richard Schollar]

If you go in the VBE to Tools>References do you have anything listed as MISSING in the list? If so, uncheck it.

 

[joeycg]

Hi,

The code is not working on my machine either.The error reads:
"Run time error '429':

ActiveX object can't create object"

The references I am using are "Visual Basic for Applications" and "Microsoft Office 14.0 Object Library".

The line of code that is highlighted is "With CreateObject("vbscript.regexp")". Any help would be much appreciated.

Thank you,
Joey

 

[Norm of Montreal]

Works fine, but still some special characters left, but will work on that later using the other code available.
but if I click the refresh button, using an ODC to a sharepoint site, the HTML comes back.
How can I automate this with some button, I am doing this for 100 users of my spreadsheet, using pivot tables and slicers...
Thanks,

Norm of Montreal