Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Jan 2001
    Location
    Crestview Hills, Kentucky, USA
    Posts
    84
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel Sorting (2003)

    The first column of data contains part numbers. Some begin with a letter, some begin with a number, some have hyphens, etc.

    I want to sort the data by the first character, then the second character, etc.

    What format and special sort do I need?

    Thanks,
    Craig.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel Sorting (2003)

    Are there part numbers that Excel interprets as numbers? If not, a normal sort will do what you want. Otherwise, you can insert a column next to your data, and put formulas of the form

    =TEXT(A1,"@")

    in this column (you must adjust the cell reference, of course), and sort on this column.

  3. #3
    Star Lounger
    Join Date
    Jan 2001
    Location
    Crestview Hills, Kentucky, USA
    Posts
    84
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Sorting (2003)

    The problem with a general sort is that "31" will be placed ahead of "1234." Even changing the column format to "Text" doesn't change this. The formula you suggested will work, but my client hass 200+ worksheets and she would like Excel to sort "like Quattro Pro did."

    Thanks,
    Craig.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel Sorting (2003)

    I guess it's impossible to satisfy everybody. Many users would complain if numbers in a list also contains text items would be sorted as text, but that is precisely what your client wants...

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Excel Sorting (2003)

    You can use something like:

    =IF(ISNUMBER(A1),TEXT(A1,"0000"),A1)

    Change the number of zeroes as appropriate. the leading zeroes will make it sort correctly. This will not help if you have something like N31 vs N1234 and want N31 to come first. If that is the case a more complicated scheme to extract out the number will be necessary.

    Steve

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

    Re: Excel Sorting (2003)

    You could use a macro like this which sorts on column A:

    <code>
    Public Sub AlphaSort()
    Dim lLastRow As Long
    Application.ScreenUpdating = False
    lLastRow = Range("A65536").End(xlUp).Row
    Range("A1").EntireColumn.Insert
    Range("A1").Formula = "=TEXT(B1,""@"")"
    Range("A1:A" & lLastRow).FillDown
    Range("A1:IV" & lLastRow).Sort Key1:=Range("A1"), Order1:=xlAscending
    Range("A1").EntireColumn.Delete
    Application.ScreenUpdating = True
    End Sub
    </code>

    See this <post:=118,382>post 118,382</post:> to put the macro in Personal.xls where it can be used in all 200+ workbooks.
    Legare Coleman

Posting Permissions

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