Copy and Paste format issue

[jamesdowell]

Hi Lovely People of Mr Excel,

HSBC won't let me download more than 90 rows of transactions in excel format. This is an issue when I want to get every transaction for 2013 from my current account. So what I have done it view my previous statements which load up in the browser in whatever format that is...

I have copy and pasted all that data from all months into a spreadsheet. It looks great BUT the problem is that I cannot sum any of the figures. I think this is a formatting issue, something like, because I have copy and pasted the data then excel cannot see it as a figure it can calculate.

I have tried changing the format of the cells to accounting and currency and all the others but nothing gets them to sum.

Has anyone had this issue and how can I solve it?

Thanks in advance.

JamesDowell

 

[Jonmo1]

Welcome to the board..

Try copying a Blank Unused cell
Highlighting all the numbers in question
Right click - Paste special - Values - Add - OK


Hope that helps.

 

[jamesdowell]

Thanks for the welcome. I've heard good things about this place, your speedy response has made a good first impression.

I tried what I thought you meant but it didn't work. So I copied a blank unused cell, then selected all the cells I want to sum and doing the paste special thing. Nothing happened in any of the cells...

I think I've misunderstood what you've recommended.. Can you advise?

 

[Jonmo1]

It was just hoping the numbers were just "numbers stored as text"
A common annoyance in excel.

That method is intended to convert those to real numbers by adding 0 to them (a blank cell = 0)

so in the PasteSpecial Window, select Values AND select ADD..

 

[jamesdowell]

I'm not sure I'm doing it right but it's not working.
I am using a mac and would like to upload a sample of the data, is there a way?

 

[Jonmo1]

Shouldn't make a difference if it's MAC or PC.
No, you can't upload files here.

Can you copy/paste some of the values from Excel to the thread here?

 

[jamesdowell]

[TABLE="width: 65"]
<!--StartFragment--> <colgroup><col width="65" style="width:65pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 65"]100.00 [/TD]
[/TR]
[TR]
[TD="class: xl65"]101.85 [/TD]
[/TR]
[TR]
[TD="class: xl65"]36.00 [/TD]
[/TR]
[TR]
[TD="class: xl65"]4.30 [/TD]
[/TR]
[TR]
[TD="class: xl65"]152.00 [/TD]
[/TR]
<!--EndFragment--></tbody>[/TABLE]

 

[jamesdowell]

Hi - been no further update because I imagine what I pasted wasn't helpful!
So I've found the issue is that there is a trailing space after all the figures, I've used the trim function but it doesn't work... any suggestions?

 

[Jonmo1]

Trim will return as a "Number stored as text", not a real number.
So you still will be unable to sum them.

Try =TRIM(A1)+0

If that still doesn't help
The trailing space is probalby not actually a space, but some other non printable character.

Try
=LEFT(A1,LEN(A1)-1)+0

 

[jamesdowell]

Sadly neither work... I have to press F2 then backspace then enter manually to delete in all 600 rows!