Do you have time?

[Guest]

Hi:

It's For Excel XP.

Suppose you have an XLS file where the columns were set to text format.
How can I reset the format to numbers with 2 decimal places?

Of cource, I could select the entire column and do it by mouse right
clicking and reformat it there.

But if I do it this way (may be the right way), when I try to SUM the values
I only get cero.

So, in order to get the real sum, I have to double click on each numeric
cell above it.

Is there any a straignt forward way to do this conversion?
This becomes even clumsier when I have 62,000 rows!
Wow!! I don't have the time to bouble click on all of them...

any help?




--
Thanks,

Rick.

"Every problem needs a fast and efficient solution"

 

[MartinW]

Hi Rick,

To convert text to numbers,
Copy a blank cell
Select your entire range of data
Right click on the selected range
Click Paste Special
Check Add
and OK out

For the decimal places,
While your data is still selected
On the formatting toolbar
Click on the 'Increase decimal' or 'Decrease decimal' toolbutton.
If you can't see these buttons you may have to click on the
more buttons option at the far right of the formatting toolbar.

HTH
Martin

 

[Guest]

Hi Rick,

You might like to know what the problem is - the cells were formatted as
text before the numbers were entered so the numbers are text. Changing the
format of the cells only prepares the cells for numbers it does not go back
and convert the text numbers to numbers.

You can:
1. Change the cell format to numbers and reenter all the numbers - they will
now be numbers - not a good choice
2. Open the Error Checking drop down (if you are using 2002 or later) and
choose Convert to Number. This will work against a range if you select the
range first. - pretty good!
3. You can use any of the paste special commands Add, Subtract, Multiple, or
Divide. With the first to copy an empty cell, with the last two copy a cell
containing the value 1. In each case select all the text numbers and use
Paste Special. This is the best method because it works in all versions of
Excel, because it is 50 times faster than #2 above, and because it work in
cases where other methods fail.
4. Depending on the numbers you can also use the Data, Text to Columns
command.