Excel 2000 text formatting
 

We have a excel sheet and are trying to sort a column in ascending order, the
problem is the way the data is merged into it, the numbers are in the same
format but are not sorting properly, they end up sorting into several groups
(10,12,13,14,11,15,16,17,7,8,9,18) excel 2003 is able to change the format
quite easily but unfortunatly all the people (to many to simply roll out 2003
to) having the problem are using 2000, I have tried to format them into the
same format, general\text\numbers tried em all but nothing happened, the only
way I can get them to sort properly is to change it all into numbers (no
negative, 2 decimals) then double click each box which formats them, which is
quite tedious and annoying and given the fact some of these sheets have
hundreds of numbers in this isnt practical. I have also tried the format
brush aswell but nothing happened. Just wondering if anyone has any ideas on
what i can do here or whether there is a site i can go to that explains how
to fix this, microsoft help and other pages have brushed over the issue but
none of the solutions worked.

Excel 2000 text formatting
 

Karl

Don't know if this will help, but I don't think it will hurt. In an out of
the way cell place the number 1. Select that cell and copy it. Now select
the range you are having trouble with, gotoCopyPastePaste
SpecialMultiply, and ok out. This "encourages" excel to make everything in
the range a number. Go clean up that cell with the 1 in it.

Hope this helps you

Mike Rogers


"Karl" wrote:

We have a excel sheet and are trying to sort a column in ascending order, the
problem is the way the data is merged into it, the numbers are in the same
format but are not sorting properly, they end up sorting into several groups
(10,12,13,14,11,15,16,17,7,8,9,18) excel 2003 is able to change the format
quite easily but unfortunatly all the people (to many to simply roll out 2003
to) having the problem are using 2000, I have tried to format them into the
same format, general\text\numbers tried em all but nothing happened, the only
way I can get them to sort properly is to change it all into numbers (no
negative, 2 decimals) then double click each box which formats them, which is
quite tedious and annoying and given the fact some of these sheets have
hundreds of numbers in this isnt practical. I have also tried the format
brush aswell but nothing happened. Just wondering if anyone has any ideas on
what i can do here or whether there is a site i can go to that explains how
to fix this, microsoft help and other pages have brushed over the issue but
none of the solutions worked.

Excel 2000 text formatting
 

Karl

Format all to General then copy an empty cell.

Select the "numbers" range and Editpaste special(in place)AddOKEsc


Gord Dibben MS Excel MVP

On Wed, 14 Feb 2007 18:59:05 -0800, Karl wrote:

We have a excel sheet and are trying to sort a column in ascending order, the
problem is the way the data is merged into it, the numbers are in the same
format but are not sorting properly, they end up sorting into several groups
(10,12,13,14,11,15,16,17,7,8,9,18) excel 2003 is able to change the format
quite easily but unfortunatly all the people (to many to simply roll out 2003
to) having the problem are using 2000, I have tried to format them into the
same format, general\text\numbers tried em all but nothing happened, the only
way I can get them to sort properly is to change it all into numbers (no
negative, 2 decimals) then double click each box which formats them, which is
quite tedious and annoying and given the fact some of these sheets have
hundreds of numbers in this isnt practical. I have also tried the format
brush aswell but nothing happened. Just wondering if anyone has any ideas on
what i can do here or whether there is a site i can go to that explains how
to fix this, microsoft help and other pages have brushed over the issue but
none of the solutions worked.