Ok, seems simple, but this is really doing my head in

Cut and pasted (from web browser) a table of figures and put in Excel.

Re-formatted the worksheet, cleared all previous formatting, and put into a sensible shape.

Trying to add up a column, and it won’t recognise the numbers in the column ie that they are in there; Excel seems to think the cell is empty. If I manually over-type the same number it recognises this, and add’s it to the total.

I’ve tried to copy out the numbers, put into word, clear formatting and paste back in – same problem.

Any ideas how I can get Excel to recognise the cells as having a number in them?

Here’s a selection of data from spreadsheet.

26/02/2013 VIS LV INSUREFRIZZEL 336.77 
26/02/2013 VIS INT’L 0078260494 35.40 
26/02/2013 VIS INT’L 0078260493 37.58 
26/02/2013 VIS INT’L 0078260495 40.75 

Is it formatted as text? Right click the column and click format cells choose a number.

If you click on the lefthand column does all the data appear in there?

If so you need to convert so its using multiple cells per line of data.

Looks like you’ve got a space after the number. Try removing this

Like Hobster says, if all the text is in a single cell, you can use the “Text to Column” function to split it to multiple ones.

It looks like you’ve got all the text in each line as text – so the cell actually contains “26/02/2013 VIS LV INSUREFRIZZEL 336.77 “

You need to split it up into columns by clicking Data then “text to columns”

Thanks for the replies –
It’s not a good pasta/pic in my OP, it is in separate columns, and there is no space on the left hand side of each column either…..

Still stuck….

Try copying the text again, paste it into notepad then copy that and paste it into a new spreadsheet (and then probably do the data->Text to columns bit)

Sometimes, there’s funny formatting. Pasting it into notepad will remove all formatting.

As mentioned above, I would suspect it thinks the numbers are text. Even if you format the column it doesn’t always make the conversion until you edit the text in each cell(so for example if you type in the number).

If the list is short then go down each cell in turn pressing F2 then enter, if it’s a long list then in another column just multiply the value by 1, this should convert to a number.

=VALUE(…)

will give the numerical value of a text cell,.

One neat way to see if numbers are being stored as text is to check there is no formatting on the cell. Then text will be left alligned and numbers will be right alligned.

and there is no space on the left hand side of each column either…..

But what about on the right hand side?

Looks like you’ve got a space after the number

^ As above, notepad is your friend. Excel is a %#£&$”! fascist.

apply and reverse a mathematical function to the text number. Excel is then able to treat it as a number.

ie. =A1*1000/1000

Use TRIM if you need to clear unnecessary spaces.

Use TRIM if you need to clear unnecessary spaces.

Or find & replace e.g. find ” ” & replace “”.

…Or type a ‘1’ into a spare cell, copy it and ‘paste special > multiply’ over all the numbers-stored-as-text cells. In many cases this forces them to numeric format.

As jletch said – try = VALUE(<cell>) in an adjoining column

I’m guessing that when you double click on the cell you’ll see the value prefixed with a ‘ which will mean it will ignore any attempts to format it as a number.