Trouble changing data format

Hi All

I've copied some data into Excel 2010 from HSBCnet and it has populated correctly. However, the numbers aren't being recognised as numbers by Excel meaning I can't use them in any mathematical formulas and when I highlight the cells I am not presented with a total amount, just a number telling me how many cells are selected. I have tried changing the format of the relevant cells from the regular 'format cells' menu but this hasn't helped.

Could anyone point me in the right direction on what to do?

Cheers

Steve

Hi Brumbot,

I just tried your suggestions but using both these methods returns a #VALUE error.

Thanks for replying though!

If you highlight the cells is there an error button? If so, one of the options may be "convert to number." Before copying the data, you may try to change the field to Text, then paste the data in to the spreadsheet. At that point, the error notification may pop up once you highlight the field.

If not, try this:

With data in A1, put in B1

=VALUE(TRIM(CLEAN(A1)))

Then fill-->down.

Hi guys

Essentially what I see is exactly what I copied from the bank system. When I paste the data I select Match Destination Format as the pasting option so the data is separated into individual columns. Even when I do this the numbers automatically paste with commas (i.e. 24,137.75), but I am unable to do anything else to manipulate any of the data unless I manually enter it to another column.

Sluwx04, I tried the trim/clean formula but again received a #value error.

I appreciate the help!

I've managed to solve the problem.

Thanks for all the help everyone!