Format Cells Does Nothing

[Steve]

I have an Excel 2000 worksheet that a user created by typing data into it.
Visually, it has two columns that contain integers such as 100, 250, 500,
700, etc. When I highlight the cells, Excel usually provides a sum in the
lower right hand status bar - however, in this case it displays nothing. I
also do a sum function on this column and Excel returns a value of zero even
though the values add up to 15750. Visually, I can see this columns are
left-justified and have no decimals which is not typical for standard Excel
number formatting. So, I highlight the cells or I hightlight the whole
column and choose format cells and format them as a number with two decimal
places. However, it does nothing to cells - they remain left-justified with
no dec places and the highlighting or sum functions do nothing. I also tried
entering a calculation such as A2+A3+A4 where A2=100, A3=150, and A4 = 300 -
Excel returns ZERO. I've tried several other calcs as tests, I have tried
changing the cells to format of currency, date, text then back to number,
general, etc - but nothing has any impact. If I format the cells as numbers
and then re-hand type in each value to the cell, then everything works fine.
However, I obviously do not want to hand re-type all these values in. Anyone
with any ideas what is going on and how I can correct it? I have this issue
on a few spreadsheets but certainly not all spreadsheets.

thanks

 

[Peo Sjoblom]

Left aligned without any alignment formatting means text, if the user
really typed in the numbers a fairly easy fix would be to first format it as
General or Numbers, then copy an empty cell, select the numbers and do
edit>paste special and select add..

 

[Paul]

I have an Excel 2000 worksheet that a user created by typing data into it.
Visually, it has two columns that contain integers such as 100, 250, 500,
700, etc. When I highlight the cells, Excel usually provides a sum in the
lower right hand status bar - however, in this case it displays nothing. I
also do a sum function on this column and Excel returns a value of zero even
though the values add up to 15750. Visually, I can see this columns are
left-justified and have no decimals which is not typical for standard Excel
number formatting. So, I highlight the cells or I hightlight the whole
column and choose format cells and format them as a number with two decimal
places. However, it does nothing to cells - they remain left-justified with
no dec places and the highlighting or sum functions do nothing. I also tried
entering a calculation such as A2+A3+A4 where A2=100, A3=150, and A4 = 300 -
Excel returns ZERO. I've tried several other calcs as tests, I have tried
changing the cells to format of currency, date, text then back to number,
general, etc - but nothing has any impact. If I format the cells as numbers
and then re-hand type in each value to the cell, then everything works fine.
However, I obviously do not want to hand re-type all these values in. Anyone
with any ideas what is going on and how I can correct it? I have this issue
on a few spreadsheets but certainly not all spreadsheets.

thanks

The 'numbers' are not numbers, but text strings. To convert them to numbers:
1 Make sure their cells are formatted as general or number (not text).
2 Copy a blank cell (also not formatted as text).
3 Select the 'numbers' and use Edit > Paste Special > Operation, Add.

 

[Arvi Laanemets]

Hi

Enter a number 1 into some cell and copy it
With copied cell marked, select the range with your data, right-click on it,
and select PasteSpecial.Multiply from drop-down menu. It's all.

 

[Steve]

Thanks for the help! Appreciate the response!

Left aligned without any alignment formatting means text, if the user
really typed in the numbers a fairly easy fix would be to first format it as
General or Numbers, then copy an empty cell, select the numbers and do
edit>paste special and select add..

--

Regards,

Peo Sjoblom