Hi,

I highlighted one entire worksheet, Formatted all the cells as Text and then
pasted in to this worksheet a set of text and numbers. Then, I highlighted
one entire column where all the values are numbers and then Formatted this
column as Numbers.

1) But, Excel would not "display" the contents in Number format. For e.g.,
some of the numbers still look like 00100 whereas I would like to see the
preceding zeroes eliminated and displayed as 100; these numbers are also
left aligned.
2) When I enter a formula to add these numbers, for e.g., as =sum(b1:b3),
the formula does not add the numbers. The formula's result is 0.00. I would
like the formula to add all the numbers and show the sum.

If I place the cursor on each cell in this column and click F2 and then
Enter, I am able to get the desired Number format for every cell. I hate
having to do this for every cell in the column!

Is there a way to change the format of an entire column of cells from Text
to Number and also see the contents of every cell formatted as Numbers? I
would very much appreciate your help on this.

Sincerely,
Subbiah.