Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Removing HTML code from Excel cell

This is a discussion on Removing HTML code from Excel cell within the Excel Questions forums, part of the Question Forums category; I have a report that comes out of one of our web-based systems in which I that contains user input. ...

  1. #1
    New Member
    Join Date
    Apr 2009
    Posts
    33

    Default Removing HTML code from Excel cell

    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?

  2. #2
    MrExcel MVP
    Moderator
    Rediscovering the love
    Richard Schollar's Avatar
    Join Date
    Apr 2005
    Location
    UK
    Posts
    23,677

    Default Re: Removing HTML code from Excel cell

    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 by Richard Schollar; Jul 27th, 2009 at 01:04 PM. Reason: corrected typo in code
    Richard Schollar

    Using xl2013

  3. #3
    New Member
    Join Date
    Apr 2009
    Posts
    33

    Default Re: Removing HTML code from Excel cell

    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?

  4. #4
    MrExcel MVP
    Moderator
    Rediscovering the love
    Richard Schollar's Avatar
    Join Date
    Apr 2005
    Location
    UK
    Posts
    23,677

    Default Re: Removing HTML code from Excel cell

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

    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.
    Richard Schollar

    Using xl2013

  5. #5
    New Member
    Join Date
    Apr 2011
    Posts
    2

    Default Re: Removing HTML code from Excel cell

    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

  6. #6
    MrExcel MVP
    Moderator
    Rediscovering the love
    Richard Schollar's Avatar
    Join Date
    Apr 2005
    Location
    UK
    Posts
    23,677

    Default Re: Removing HTML code from Excel cell

    Hi

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

    Using xl2013

  7. #7
    New Member
    Join Date
    Apr 2011
    Posts
    2

    Default Re: Removing HTML code from Excel cell

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

  8. #8
    MrExcel MVP
    Moderator
    Rediscovering the love
    Richard Schollar's Avatar
    Join Date
    Apr 2005
    Location
    UK
    Posts
    23,677

    Default Re: Removing HTML code from Excel cell

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

    Using xl2013

  9. #9
    New Member
    Join Date
    May 2011
    Posts
    4

    Default Re: Removing HTML code from Excel cell

    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

  10. #10
    New Member
    Join Date
    Jul 2012
    Location
    Montreal
    Posts
    1

    Default Re: Removing HTML code from Excel cell

    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

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com