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]

Is it just ONE trailing character after the number? Or multiple?


Also, just to clarify..
These formulas don't 'REMOVE' the extra character from the original cell.
Instead, they CREATE a new value in a different cell based on the original.

So if A1 is your original data
and in B1 you puut
=TRIM(A1)+0 (or the other formula)

Then it's B1 that you should now be trying to sum, not A1.

 

[jamesdowell]

Yes but both formulas deliver an error of #VALUE!

 

[Jonmo1]

If this returns #Value! error
=TRIM(A1)+0
It's because your trailing spaces are not really space. They're some other character.

If this returns #Value! error
=LEFT(A1,LEN(A1)-1)+0
It's because there is more than one of those trailing 'spaces'
The formula is designed to only remove 1 character from the end of the value.


for those given example values, say 4.30
What does this return
=LEN(A1)
Where A1 is the cell with 4.30

If it were a real number, it would = 4.