Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I have a spreadsheet, which we have recorded a stock count. My problem is sorting into part number order. Say I have 1A17, 1A2, 1A36, 1A125, 1A56. When I sort, they are coming out as

    1A125
    1A17
    1A2
    1A36
    1A56

    And not

    1A2
    1A17
    1A36
    1A56
    1A125

    as I hoped. Is there a way of getting round this? Or will I just have to live with it? At the moment, the spreadsheet just contains 1 number range (1A), but future spreadsheets could contain other ranges (2B, 2C,....)

  2. #2
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    990
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Add a column which removes the "1A" section of the part 'description' (it is not a part 'number', and that's your problem) and converts the result to a number, then sort on that.

    The first operation is simple using string manipulation functions. The second is most easily done by multiplying the result by 1, which forces Excel to treat the result as a number.

  3. #3
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    Assume the part numbers start in Cell A2, try this formula in some other column beginning in Row 2 and copy down.......
    =VALUE(RIGHT(A2,LEN(A2)-2))

    Highlight all the data to be sorted and sort on the new column.

Posting Permissions

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