 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / June 2005How do I convert copy and paste from web to data.
The Abbey in its infinite wisdom chooses not to give their credit card customers the same options as they do their current account customers. Specifically they do not allow their credit card customers the ability to download their statements. The only way I can get this info electronically is to copy and paste from my browser to an Excel spreadsheet. Unfortunately what you get doing this is not data that can be manipulated! Does any know how I can convert what you get from this to meaningful data that can be summed etc? Thanks. Chris. P.S. I have asked for this basic facility, but apparently there is not sufficient demand! If you have an Abbey credit card, perhaps you could email them at http://www.abbey.com/contact_abbey/types_contact_cc.htm asking for this option. If you don't perhaps you could email them expressing an interest in getting a card, which gives up to 2% cash back, and enquiring about the option of downloading statements, making it clear that without this facility you'll go elsewhere for your new card ;-) Have you tried to right click on the table and export to Excel?  SignatureDon Guillett SalesAid Software donaldb@281.com > The Abbey in its infinite wisdom chooses not to give their credit card > customers the same options as they do their current account customers. [quoted text clipped - 18 lines] > it clear that without this facility you'll go elsewhere for your new > card ;-) I selected all of the relevant cells in my browser, then right click/copy, then paste into excel. Everything looks OK, i.e. words and numbers in the right places (cells), legible etc, but you can't do anything with it, e.g. Sum, as excel doesn't appear to recognise it as numbers. I've tried re-formatting the cells but still no go! > Have you tried to right click on the table and export to Excel? Can you give some more details on the problem? Does all the data for a row end up in the first column? If so, try DATA menu | TEXT TO COLUMNS. Data looks OK, i.e. in correct Columns, Rows and Cells, but you can't do anything with it, i.e. Sum etc, since excel doesn't appear to recognise it as data. I've tried re-formatting the Cells, but to no avail. > Can you give some more details on the problem? > > Does all the data for a row end up in the first column? If so, try DATA > menu | TEXT TO COLUMNS. Hi Chris, The data coming from HTML is a dead give away that you probably have Char(160) in HTML that may be or it might simply be as a single character with that value. Reformatting cells has no effect in changing number formatting between numbers and text or between text and numbers until the data is reeentered. The easiest way to reenter is to hit F2 then Enter for a single cell. Coming from HTML the data is still modified you would have to retype what you think you see the value as. see http://www.mvps.org/dmcritchie/excel/join.htm#trimall for the problem identification and solution. The TrimALL macro will take care of the removal of spaces and char(160) from the left and right and reentry so is very practical for large areas of data, but should be used with some caution (on a copy of your data) if you are not familar with how Excel treats data that could be fractions, or dates. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm > Data looks OK, i.e. in correct Columns, Rows and Cells, but you can't > do anything with it, i.e. Sum etc, since excel doesn't appear to [quoted text clipped - 5 lines] > > Does all the data for a row end up in the first column? If so, try DATA > > menu | TEXT TO COLUMNS. Thanks David. Problem is caused by a trailing blank space, once I remove that all is OK. I'll play with TrimALL to find out how to make this work. Thanks again. Chris. David. I've tried to install your trimall Macro, but no joy. Probably due to me not understanding things properly as I've never done anything like this before. I've tried to create my own Macro, as follows, again something new for me. Tools, Macro, Record New Macro Give it a name and assign a shortcut key in the dialogue box that pops up. Click on first amount cell in my spreadsheet Click on the function bar? (where it displays the content of the cell) Cursor appears one blank space to the right of the last character in the cell, Backspace, to delete the blank space Return, to populate the cell with the amount as data Tools, Macro, Stop Recording Problem is when I run this Macro on another cell it ends up with the modified contents of the cell that I used when I created the Macro, not the modified contents of what the cell I ran the Macro from. Any suggestions? I'm sure if I could get a simple Macro like this or something similar to work I could solve the original problem. I've tried this with both Relative cell values and Absolute cells references. Or do you have the complete idiots guide to installing trimall, bear in mind I've no experience of VBasic or anything similar. The cells I need to convert all contain currency amounts, in pounds.(dot)pence form, no currency symbol. Thanks for your help so far at least I now know what the problem is, if not how to solve it. TIA. Chris at wits end! Hi Chris, Recording a macro just gives you an idea of the kind of statements that you might use, it hardly ever results in code completely usable for use as a permanent macro. You got your macro installed and you ran it, so you are on the right track. You don't need a shortcut to run a macro you can use the tools, macro, macro or use Alt+F8 I actually use a toolbar button to invoke that dialog or another button with the macro assigned to it (temporary). You can replace the macro you created with TrimALL macro or place the TrimALL macro after the one you created. For permanent you want the macro in your personal.xls there is a box at the bottom on your dialog when you record a macro. See http://www.mvps.org/dmcritchie/excel/getstarted.htm --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm > David. > [quoted text clipped - 38 lines] > > Chris at wits end! David. Finally got your TrimAll to work. Learned a bit about Macros, Visual basic Editor and Microsoft Script Editor along the way. Thanks for your help and patience; I can now manipulate the crappy Abbey VISA Credit Card statements. I still don't have personal.xls on my machine - something for another day. Thanks. Chris. Hi Chris, Good to know it's working and you didn't give up. Macros will be a lot easier now. But you will want to let the macro recorder create a personal.xls for you so you can put your good macros in there for use in any open workbook. --- David McRitchie, Microsoft MVP - Excel My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm <chris.a.mitchell@btinternet.com> wrote in message > Finally got your TrimAll to work. When you click on the cell, do you get a popup that states: "Number stored as text" - if so, you can correct it from within that popup box. If the box doesn't popup, go to TOOLS | OPTIONS, click the Error Checking Tab, and Check the box for "Number Stored as Text" if it is not already checked. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.