How do I convert copy and paste from web to data.

C

chris.a.mitchell

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 ;-)
 
C

comish4lif

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.
 
C

chris.a.mitchell

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!
 
C

chris.a.mitchell

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.
 
D

David McRitchie

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.
 
C

comish4lif

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.
 
C

chris.a.mitchell

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.
 
C

chris.a.mitchell

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!
 
D

David McRitchie

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
 
C

chris.a.mitchell

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.
 
D

David McRitchie

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top