Excel question

[BenElliottRacing]

Writing up my accounts and have a snag.

I have copied my bank statements across from web page onto excel spreadsheet. Now that I want to try and do some sums with the columns they are all totalling 0.

If I retype a figure then the sum at the bottom goes up by that amount.

Short of retyping every figure in 9 months of bank statements how can I get excel to see the pasted text as numbers?

Tried converting all fields to 'number' but no success. Spent a bit of time on help but couldn't find anything useful.

TIA

[tank slapper]

Have you checked to see if your bank lets you export the data in a more usable format than copy/pasting the text?

Natwest allow you to download in a few different formats - Microsoft Money, Quicken, Sage, and CSV etc.

[BenElliottRacing]

Tried that. Used to bank with Natwest so went looking for the download option. Am now with HSBC which gives you a print option but little else.

[Nicholas Blair]

Try this, should work.....

In an empty cell, enter the number 1.
Select the cell, and on the Edit menu, click Copy.
Select the range of numbers stored as text you want to convert.
On the Edit menu, click Paste Special.
Under Operation, click Multiply.
Click OK.
Delete the content of the cell entered in the first step.

cheers

nick

[Big_Dog]

I use HSBC. Down the bottom of the page is a download data type option. Download in Excel format. Job done. Just had a year to do. Lucky me. It really isn't the best designed online banking.

Edited by Big_Dog on Saturday 1st January 23:38

[dblack1]

sounds to me like you have individual cells selected for your sum.
Try this. make 2 cells on a new sheet A1 ($0) and A2 ($0) then make a 3rd cell A4 (=sum(A1:A2)). when you put in the new information, you insert new cells (not paste) into excel (excell will automatically expand the range of your sum). to make this easier, you can use freeze panes and hide cells. you dont have to do it exactly like i did, but thats how i would do it. I would also format it as currency, not a number (i think it looks more neat).

Edited by dblack1 on Sunday 2nd January 00:58

[Landlord]

Nicholas Blair said:

Try this, should work.....

In an empty cell, enter the number 1.
Select the cell, and on the Edit menu, click Copy.
Select the range of numbers stored as text you want to convert.
On the Edit menu, click Paste Special.
Under Operation, click Multiply.
Click OK.
Delete the content of the cell entered in the first step.

cheers

nick

This will work - assuming that the post regarding being able to download your statement doesn't help.