Amazon offers

Strip off Html tags from excel


When data is exported from any web page into excel or from any other sources into excel. It gets copied with html tags. This is making it difficult to read it. If there are few rows then we can remove them manually but if we there are more number of rows then we can go for below steps.

Follow these steps:

1.    Open up the VBE with Alt+F11.
2.    Locate your workbook in the top left Project Explorer window. Right-click on your workbook and choose Insert>Module.
3.    Paste the below given code in to the code module that will open.
4.    Go back to Excel, select the cells you want to clean and activate the macro with Tools>Macro>Macros. (Macro can also be activated by clicking Run icon in Visual Basic).
5.    All the html tags will be replaced.
6.    But sometimes some cells will be displayed as hashes. In such cases, select the Description column again and select Home > Clear > Clear Formats. This will get back your description.





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

Comments

  1. APDecember 1, 2012 at 12:30 PM

    Awesome stuff !
    Is there any way to avoid hashes ?

    ReplyDelete
  2. AnonymousJuly 30, 2014 at 1:55 AM

    Thanks for adding step #6...I was struggling with this and have finally finished!

    ReplyDelete

Post a Comment

Popular posts from this blog

Input Data Validation

Every web application is expected have at least one submit form. Data entered in form is transferred as input to database or to any other application and will decide the output. So, input data plays a very important role in retrieving the output data. Such data needs to be validated by the code. And tester has the responsibility of validating such data. At this point tester thinks about how can the validations be done and what type of data should be provided to validate the input field. Test data needs to be created and passed to the form through the automation tool or manually. Here are few steps or guide lines to validate the data. Let’s consider a sample form which acts as input to signing in as new member in a social networking site like facebook. It contains different types of input fields – Numeric, String, Alpha Numeric, date, preset values (from drop downs or check boxes), file uploads and Email addresses. Name: String input field Password : Alphanumeri
Read More

How to test search functionality

Every web application by default will contain search feature in it. Many search engines’ main functionality is search feature. The parameters to be considered while developing this feature or testing this feature are not limited. There are many factors which should be looked in when checking the coverage and usability of the search text box in the application. Preparation before testing the search functionality 1.        Note down the valid input details for the search feature. 2.        Find out the minimum and maximum range. 3.        Find out the depth of the search – document search, word in a document, image search 4.        Should document search display the size of the document also? Similarly for image? 5.        Any advanced search features like document or image type selection to refine the search are available? 6.        General set of input which can be given are a.        A-Z b.       a-z c.        0-9 d.       {      [              (       
Read More

Scrum Methodology - Quick Info

Image
It is one of the unique methodologies of agile methodology. Scrum  It is an   iterative, incremental   framework for project management . It   is a loose set of guidelines that govern the development process of a product, from its design stages to its completion. It aims to cure some common failures of the typical development process like a. chaos due to changing requirements, unrealistic estimates of time, cost and quality of the product. Meetings 1.      Daily Scrum meeting or daily stand up meeting: agenda:  what is done, what is planned for today, what are the problems in accomplishing the goal? 2.      Sprint planning meeting : Before each sprint cycle (7-30 days)starts, prepare sprint backlog, timelines, 3.      Sprint review meeting: done at the end of sprint cycle, review work completed and not completed, demo the work completed, do not demo incomplete work, 4 hour time will be allocated. 4.      Sprint retrospective meeting: done at the end o
Read More