Results 1 to 3 of 3
  1. #1
    Lounger
    Join Date
    Jan 2001
    Location
    Evansville, Indiana, USA
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Leading zero's and sort

    I created a spreadsheet with job numbers and other data pertaining to those numbers. Many of the job numbers start with a zero. The cells are formatted as text to retain the zero. How do I get an ascending sort to recognize the leading zeros should come before the 1's and not after the 9's? I am using Excel 97 SR2.

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Leading zero's and sort

    If I create a column as you describe, the column does sort with the zeros ahead of the ones. Are you sure that the numbers with no leading zero do not have a space in front of the first digit?
    Legare Coleman

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Leading zero's and sort

    I would suggest that if at all possible you format the data as either Number or General. If you want leading zeros to show use the custom format feature, entering the number of zeros that represent the length of the number, e.g for 5 digit number enter 00000 as the custom format, that would show number 1234 as 01234.

    Text formatting in Excel seems unpredictable to me, and I think for it to work properly the cells need to formatted as text before the data is entered.

    Andrew C

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •