formatting excel currency

[Troopa]

hi all,

can someone advise a quick and easy way to convert my cells so that I can use the SUM function to add the values?

Basically when I copy my bank statement from the internet into Excel it adds it as General under the number tab (format cells) when I change this to Currency SUM still doesn't work and I think this is because somehow it is still text?

I tried just removing the £ from each cell but this made no difference and the only way I could get the SUM to work was by manually typing the fugure over the top of the figure that's in there already...

Is there an easy way to convert the cells other than what I've tried?

thanks in advance

 

[AnotherHiggins]

Use the "Times 1" fix that has been discussed here on several occasions...
[ul][li]Go to an empty cell and type "1"[/li]
[li]copy that cell[/li]
[li]select the data that you want to change to Number Format[/li]
[li]right click and choose "Paste Special"[/li]
[li]tic the box by "Mulitply'[/li]
[li]click ok[/li][/ul]

That should do it!

[tt]_____
[blue]-John[/blue][/tt]

"Patriotism is your conviction that this country is superior to all other countries because you were born in it."
[tab]-George Bernard Shaw

Help us help you. Please read FAQ181-2886 before posting.

 

[Troopa]

thanks john, I think that was one of the options I tried after looking through the help file but didn't work. Maybe I did something different so I will try what you suggested

 

[xlbo]

It will work - it is the standard fix for converting "text numbers" into "real numbers"

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Troopa]

this was a long time ago and I've tried it and it doesn't work? When I say it's not working, it is changing the field to a number field but then when I try to do a sum calculation on it it just shows a null value?

What can I check to find out why it isn't working?

 

[AnotherHiggins]

try typing
[COLOR=blue white]=isnumber(a1)[/color]
into an empty cell. Replace 'A1' with a cell that you have tried to convert to a number. Post back with the result of the function.

[tt]_____
[blue]-John[/blue][/tt]

Help us help you. Please read FAQ181-2886 before posting.

 

[Troopa]

thanks John, the result is FALSE
when I look at the cell under - Format Cells - Number, number is selected though and I have done the exact steps as advised by anotherhiggins.

The best way I can retain the formatting when I copy it from my online statement is to paste it as HTML so is this the reason?

 

[dcompto]

Some cells may contain spaces or unprintable characters which you can strip out using Dave McRitchie's Trimall macro. You may then need to do the "Times 1" fix referred to above by anotherhiggins.

The macro can be found at

http://www.mvps.org/dmcritchie/excel/join.htm#trimall