Removing HTML code from Excel cell

sarayewo

New Member
Joined
Apr 28, 2009
Messages
33
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?
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Hi

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

Code:
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.
 
Last edited:
Upvote 0
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?
 
Upvote 0
You could just wrap the preceding code in the Replace VBA function and specify it to replace your chosen characters:

Rich (BB code):
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top