Sort number as text

  • Thread starter Thread starter JudiD
  • Start date Start date
J

JudiD

I have an excel 2000 file in column I, I has cost center
numbers, the range of numbers are as follows. 000, 050,
555, 850. I have made the cell text cells so they will
keep the preceding 0's. However when I sort them, they
come out in the following order. 555, 850, 000, 050.
 
Hi

To display numbers with preceeding 0's you don't need to change cell format
to text. Format the cell as Custom "000" instead.

When you need to convert numbers to strings somehow, use formula
=TEXT(CellReference,"000") in some adjacent column. NB! You can't use
converted values in some calculations - they aren't numbers.
 
Hi Judi
Judging by the way your numbers are sorting, you only converted the data with preceding zeros to text. You can confirm this by applying a "general" horizontal alignment to the column. Numbers will align right and text will align left

If you want the data to sort properly it must either all be text or all be numbers. If you won't have any letters in your code number, I would suggest using a number format of "000", as Arvi also suggested in his post

However, If you apply this number format now, it will not automatically convert your text back into numbers. To do this, select a blank cell and Edit>Copy. Then select the range of cells with your account numbers and Edit>Paste_Special>Ad

Good Luck
Mark Graesse
(e-mail address removed)

----- JudiD wrote: ----

I have an excel 2000 file in column I, I has cost center
numbers, the range of numbers are as follows. 000, 050,
555, 850. I have made the cell text cells so they will
keep the preceding 0's. However when I sort them, they
come out in the following order. 555, 850, 000, 050.
 
Back
Top