Change cells originally defined as Text to Numbers

[Subbiah]

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.

 

[Gord Dibben]

Format all to General.

Copy an empty cell also General Formatted.

Select the data range and Edit>Paste Special(in place)>Add>OK>Esc.

Numbers are now numbers.


Gord Dibben MS Excel MVP

 

[Subbiah]

Dear Gord:

Thank you. Looks like we are tricking Excel in to doing a math operation in
the selected cells which then makes the cells format themselves as Numbers.
Whew! Thank you very much for figuring this out! Thank you.

Why doesn't Excel readily change the format when I chance the format to
Numbers? Is there a reason? Would you know?

Sincerely,
Subbiah

 

[Gord Dibben]

Formatting just changes the appearance of whatever is in the cell(s).

It does not change the underlying value.

e.g. enter 39051 in a cell.

Format to Date and you will see 29/11/2006.

The underlying value is still 39051........you have not changed that, just
changed the appearance of 39051.

Hence, in your situation, what looks like numbers are really text and changing
formatting does not alter that fact.

Coercing the text numbers to real numbers requires the extra math operation used
in this case.


Gord

Dear Gord:

Thank you. Looks like we are tricking Excel in to doing a math operation in
the selected cells which then makes the cells format themselves as Numbers.
Whew! Thank you very much for figuring this out! Thank you.

Why doesn't Excel readily change the format when I chance the format to
Numbers? Is there a reason? Would you know?

Sincerely,
Subbiah

Gord Dibben MS Excel MVP