Excel wizards in here please

mattyj1085

mattyj1085

mattyj1085

Associate
Joined
27 Feb 2006
Posts
1,750
Location
Normanton
I have an excel sheet where each row has web page information in.

From Cell C3 and I am trying to remove the following from the start

<div style="padding-right: 0px; padding-left: 0px; float: left; padding-bottom: 0px; margin: 0px; width: 600px; padding-top: 0px; height: 300px; background-color: #f3f3f3">
<div style="padding-right: 10px; padding-left: 10px; padding-bottom: 10px; padding-top: 10px">

and

</div>
</div>

From the end

This then needs to be copied down around 300 rows to remove the div information from each page.

I have tried find and replace but it says the formula is too long and a few other ways

Does anyone know a way of removing the Div information and copying this down the sheet? (otherwise i will need to edit 300 pages individually)


Thanks
 
I haven't had a try of this in Excel but just wondering if something like Notepad++ would do this.

You could open all 300 pages in Notepad++ or alternatively break them up into groups of 50 or something depending on your PC. Then go to Search > Replace (or CTRL +H) and then enter what you want to search for and then replace, so the first line. Then specify what you want to replace it with, leaving it blank should work.

Then here is the clever bit, instead of pressing "Find", "Replace" or "Replace all" but "Replace all in all open documents". :D

Notepad++ should then look for the search string in all the open documents and replace them with a blank space. :)
 
How do i export the data from the excel file to individual an individual text document (or documents?

When i try and save it as unicode text is says "the document is not completely saved" or do i save as another format then open that using notepad ++?
 
Good question! Didn't think of that! :o

Just opened an Office 2007 Excel document in Notepad++ and it works OK. You do get some random characters but I can see the text fine.

How did you get the html code into Excel then?

If you want to do this in Excel then the Find and Replace tool is what you need. Just search for the code you want rid off and what you want to replace it with i.e. a blank space. As for making Excel select all the 300 pages worth of code, just select all the worksheets, then whatever action you carry out on the first selected worksheet will be copied across the others. Hope this helps. :)
 
its a huge database export i think (not done by me) I can open the XLS file in notepad ++ and remove and div information, but as soon as you make any changes and save then try and open it in excel it either doesnt work or messes the date in the file up.

What i need to do is keep all the data in rows exactly the same so it can be reimported back to the database. So far this involves me going through it by hand or editing each webpage (using a built in editor) individually

These are the columns

PageID AreaID HTML Text Security PageName

The HTML is what i am trying to remove (but without moving any other data (or inserting any other junk)

Thanks anyway
 
I assume that each row in the worksheet represents a webpage.

In a blank cell eg. 'H3' type the following formula '=MID(C3,x,LEN(C3)-y)' where x is the number of characters to strip off the beginning (about 265 in your case) and y is the number of characters you want to strip from the end. You'll have to find the exact values of x and y.
When you've done that, copy the formula to the relevant rows of column 'H' (use Fill Down). Select and copy column 'H' and use Paste Special, Values to paste the results back to column C.
Delete column 'H'

Needless to say, backup your original file first!
 
thanks all. neally done now, had me and 2 other developers removing the info from pages. The div info (character length) was different on some pages anyway tomsk, but thanks anyway
 
You must log in or register to reply here.
Back
Top Bottom