Choosing the Sort Region (#region)

It is best to choose your region first

For instance select    A2:H200
Note when you sort there is a box in lower left corner of the sort dialog box to indicate whether your selection includes headers (column/rows).

Sorting on a column does not automatically include keeping the row data with the column data.  If you select Column A and then sort Col A that is all you sort.

Excel has a little trick that if you select a single cell, and only a single cell, Excel will extend the range to the current region.
    Select the current region    CTRL+SHIFT+* The current region has boundaries at edges of worksheet or up to a blank column or row boundary (MS definition of ISBLANK). To include all of the data that is next to another cell with data next to a selection.  You hardly ever want to use the current region when doing a sort, so read the next topic to avoid risking your data.

If you don't choose your region first

It is best for you to select the region yourself.  You may select the entire spread sheet, or entire columns, only data in the used range will be sorted.  Note if you were writing a macro or use someone else's macro you want to make sure that processing is limited to the used area, as opposed to every cell in the spreadsheet, which may mean that you have to be careful with what you select, but the sort will always restrict itself to the used area.

Sorting on the current region (Ctrl+SHIFT+*) which is defaulted by a single cell selection is one of the worst implementations ever devised by Microsoft and indiscriminant use will eventually result in the destruction of your data and you may not notice the problem until days later when you discover you have jumbled data.  It gets worse if you have Excel 2003 and much worse in Excel 2007 (see Ctrl+A below).

Sorting by rows (#rows)

Normally sorting is by column, but you can sort by rows with the option button, be careful to put it back if you experiment.

Select All cells on Sheet before Sorting (#ctrlA)

To avoid such problems for most worksheets where you want all columns to be included (move with) the sorted data, simply select the entire sheet  (Ctrl+A).  In fact the best use of the Sort icons can be done by selecting any cell in the column you want to sort on, then using Ctrl+A, then the sort icon button.  If you have Excel 2003 your data is at more risk and the risk gets worse in Excel 2007 see shortcut keys foobar rather than risk your data using default Ctrl+A.

But wouldn't one always want to include the entire row

You should specify the entire range, and no, one does not always want to include everything on a row with the sorted columns.  There could be distinct areas on the spreadsheet for different types of data.  Here are some common things.

• Team 1 members sorted in Column A, Team 2 members sorted in column B, etc.
• A phone list that is actually 3 separate lists, 1) names and numbers, by lastname, 2) sorted by firstname, 3) sorted by phone number, each list in it's own pair of columns.

Sort toolbar buttons     (#icons)

If you only want to sort on one column and include all of the associated row the easiest way is to select a cell in the sort column, then select the region, Ctrl+A, then you can use the ascending sort (A-Z) button, the descending sort (Z-A) button, or use the Data, Sort menus.  Starting with Excel 2003 simply using Ctrl+A is not good enough and it gets worse in Excel 2007 -- your data is at severe risk, see foobar, rather than risk your data using default Ctrl+A.

Selecting a cell then Ctrl+A is the only way that I would recommend using the ascending or decending sort toolbar buttons. I am glad I found a safe way of using the sort toolbar buttons because it is easier than going through the menus if you only need to sort on a single column (with associated data).  One additional item is that in order to prevent the first row from being sorted make sure that it is bold and the 2nd row is not entirely bold this will usually meet the criteria used for automatic determination of headers in the sort.  Also helps to make sure that every column has a header label.  If it messes up you can use the UNDO button (or better Ctrl+Z).  If you have Excel 2003 your have a problem and it gets worse in Excel 2007 see shortcut keys foobar rather than risk your data using default Ctrl+A.

If you really want control of headers or no headers, then you can install macros and assign them to a button of your choice or design.

Sub SortAscending_NoHeader()   Cells.Sort Key1:=ActiveCell,   Order1:=xlAscending, Header:=xlNo, _   OrderCustom:=1, MatchCase:=False, _   Orientation:=xlTopToBottom End Sub

Sub SortAscending_Header()   Cells.Sort Key1:=ActiveCell, _   Order1:=xlAscending, Header:=xlYes, _   OrderCustom:=1, MatchCase:=False, _   Orientation:=xlTopToBottom End Sub

Sorting with DoubleClick data has headers (#dclick)

Since the sort buttons can not be assumed to include or omit headers, you would have to consider their use to be unreliable.  The following example would allow you to double-click on any cell and you would sort on that column.  Since this is a worksheet Event macro you can modify it to tailor it to your specific sheet, such as adding a KEY2, or not including the last row of data (determined by data in Column A) in the sort.  Like sorting with the buttons if you want to sort on two columns, you can sort the most minor first up to the major sort.

To install the following event macro, right click on the sheet name, view code, and place the following code within -- only one Option Explicit statement and it would be first.

Option Explicit
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  Dim LRow As Long    '-- D.McRitchie, 2006-04-01 double-click on column to sort
  'Find last row in Column A with content
   LRow = Cells(Rows.Count, 1).End(xlUp).Offset(0, 0).row
   Rows("2:" & LRow).Sort Key1:=Cells(2, ActiveCell.Column), _
      Order1:=xlAscending, Header:=xlNo, _
      OrderCustom:=1, MatchCase:=False, _
      Orientation:=xlTopToBottom
End Sub

'If you want to also omit the last row from the sort (based on Column A) use instead

     LRow = Cells(Rows.Count, 1).End(xlUp).Offset(-1, 0).row

'To include a minor sort, then also include code for a secondary key, in this case will assume that is Col A (col 1) such as for a date, but you can hard code a different secondary sort for your Key2.  After all this is specific to a sheet.

     Sort Key2:=Cells(2, 1), _
     Order2:=xlAscending,

---

Sort Sequences

---

Sort sequences (#seq)

A frequent posting involves sorting that appears to be not working correctly according to what the poster expects.  Usually the posting is strictly a matter that what appears to be a number is not a number but is text.  Text may be because the field was formatted as text, it has a quote in front to make it text, or it is mixed numbers and letters.

Numbers are sorted first, when in ascending order.

The ASCII collating (sort order) sequence places digits before letters.  In Excel and most PC software upper and lowercase letters are treated identically for sorting purposes.

Another aspect of sorting in Excel is that Blank cells are sorted at the end both in ascending and in descending sorts.  A Blank cell in Excel is what would normally be called a null cell.   A cell which has not content is a Blank cell.  Putting in a space or any character make it no longer a blank cell.  A cell may become blank again by using Edit --> Clear,  it cannot be made blank with the Del key.

Cells consisting of a different number of spaces will not sort as equal.  See   Default sort orders.

Default sort orders (#order)

Microsoft Excel uses specific sort orders to arrange data according to the value, not the format, of the data. In an ascending sort, Microsoft Excel uses this order:

•  Numbers are sorted from the smallest negative number to the largest positive number, followed by Text, and text that includes numbers.
•  Text is sorted in this order, with lowercase being equivalent to uppercase.  The following table shows only CHAR(32) to CHAR(127).

  39 '

  45 -

  32

  33 !

  34 "

  35 #

  36 $

  37 %

  38 &

  40 (

  41 )

  42 *

  44 ,

  46 .

  47 /

  58 :

  59 ;

  63 ?

  64 @

  91 [

  92 \

  93 ]

  94 ^

  95 _

  43 +

  60 <

  61 =

  62 >

  48 0

  49 1

  50 2

  51 3

  52 4

  53 5

  54 6

  55 7

  56 8

  57 9

  65 A

  66 B

  67 C

  68 D

  69 E

  70 F

  71 G

  72 H

  73 I

  74 J

  75 K

  76 L

  77 M

  78 N

  79 O

  80 P

  81 Q

  82 R

  83 S

  84 T

  85 U

  86 V

  87 W

  88 X

  89 Y

  90 Z

  Actually Microsoft Excel help indicates the following order, which is not my experience for sorting text:

  0 1 2 3 4 5 6 7 8 9 ' - (space) ! " # $ % & ( ) * , . / : ; ? @ [ \ ] ^ _ `
  { | } ~ + < = > A B C D E F G H I J K L M N O P Q R S T U V W X Y Z

•  Apostrophes (') and hyphens (-) are ignored, with one exception: If two text strings are the same except for a hyphen, the text with the hyphen is sorted last.;
•  In logical values, FALSE is sorted before TRUE.
•  All error values are equal.
•  Blanks are always sorted last. -OR- so it says but appeared to work only for lists.

In a descending sort, Microsoft Excel reverses the order of everything except blank cells, which are always sorted last.

Note   Microsoft Excel will use any custom sort order you select instead of the default sort order. -OR- so it says but appeared to work only for lists.

---

For more comprehensive information on sorting suggest the following:
      HELP --> Answer Wizard -->  sort  sorting

Sorting mixed alpha and numeric cells (#mixed)

Problem:
Sorting on column C  yields  9110 9150 0005 1243 1243
when the expected results were 0005 1243 1243 9110 9150

Comment:
Excel puts cells which are numeric in front of cells which are text.  Excel extends this a bit further in saying that fields that cells that are numeric will be sorted before cells that are not numeric.  What you think is numeric is not necessarily what Excel thinks is numeric.  For instance any *number* beginning with a zero is text.  Text cells are are sorted in ascii collating (sorting) sequence left to right (spaces included), and numeric cells are sorted as a group numerically before the grouping of text cells.

The "0005"  appears to be text because for a number to appear that way you would have to format it.  The others are not so obvious but they two are text rather than numbers.

Blank cells is the name Excel uses rather inappropriately (IMHO)  for empty (null or never used cells).  Blank  cells are sorted to the end.  A formerly used cell can be made blank using Edit->Clear but cannot be made blank simply
using the Del key.

There is a difference between a cell with one space and a cell with two spaces -- you can use LEN Worksheet Function to see the actual length.

So I expect that you probably have single quotes in front of those that don't begin with "9"; otherwise, you should see TEXT as the format.

Problem: want to sort mixed text and numbers as if text
Solution:  Create a helper column, use to following formula and fill down, then sort on the helper column.
  =IF(ISBLANK(A1),CHAR(255), IF(ISTEXT(A1),A1,TEXT(A1,"@")))

For a macro solution, format the column as text then run the Convert_Numeric_Constants_to_Text_in_Selection macro.  Afterwards your entires will all text text by your formatting.

Extract Numbers for Sorting mixed numbers and alpha, numeric prefix (#extract)

Mixed numbers and alpha codes sorted numerically using CellValue.  A suggestion from John Walkenbach.  The table at right has been sorted by column B then column A. CellValue extracts the numeric portion. In actual usage the original Column A would probably be aligned left or right for consistency. Function CellValue(c) As Double 'John Walkenbach 2001-04-25 ' misc returns number part CellValue = Val(c) End Function Anything beginning with nonumeric will yield 0, resulting in those without numbers appearing first if sorted as described.

A B C 1       2 1 1 =cellvalue(A2) 3 1a 1 =cellvalue(A3) 4 2 2 =cellvalue(A4) 5 2c 2 =cellvalue(A5) 6 2cde 2 =cellvalue(A6) 7 3 3 =cellvalue(A7) 8 3 3 =cellvalue(A8) 9 24 24 =cellvalue(A9) 10 30 30 =cellvalue(A10) 11 100 100 =cellvalue(A11) 12 100.a 100 =cellvalue(A12) 13 100a 100 =cellvalue(A13) 14 100.1 100.1 =cellvalue(A14) 15 200abc 200 =cellvalue(A15) 16 200.1 200.1 =cellvalue(A16) 17 200.1a3   200.1 =cellvalue(A17)

Sorting product code with alpha prefix and numeric suffix (#pcdigits)

Problem:
Want to sort Product Codes with alpha prefixes numerically after their alpha prefix.

The following table is sorted on Product#   A 1 Part# 2 ab14 3 ab2 4 OP1 5 OP12 6 OP1221 7 OP30 8 OP31

But is wanted as follows:   A 1 Part# 2 ab2 3 ab14 4 OP1 5 OP12 6 OP30 7 OP31 8 OP1221

The following solution was posted by Tom Ogilvy on 7JUN1999 and the results can be seen in the table following.  Column B & C can be deleted after sorting or kept as you wish.  Column D & E are included strictly to show formulas in use.  Failure to have both alpha and numeric portions may result in unexpected values.

Create two dummy columns.  Put your text portion in one column and your numeric portion in the other.  This formula will get the text portion from cell A2:

=LEFT(A2,MIN(IF(ISNUMBER(MID(A2,{1,2,3,4,5,6},1)*1),{1,2,3,4,5,6},7))-1)

It is an array formula, so enter it with Ctrl+shift+enter

This formula will get the numeric portion and uses the results of the of the above formula:   The multiplication by 1 forces entry to a number instead of text.

=RIGHT(A2,LEN(A2)-LEN(A2))*1

Adjust these to address your first row of data.  Then select both cells and double click in the lower right corner of the rightmost cell to have the formulas fill down the column.

Then sort on these columns.  Columns B and C, in the following example.

Also, in the first formula, you can replace the hard coded arrays with row(indirect("1:6"))    As coded up to 6 characters may precede the numeric portion.

=LEFT(A2,MIN(IF(ISNUMBER(MID(A2,ROW(INDIRECT("1:6")),1)*1),ROW(INDIRECT("1:6")),7))-1)

Example:

Alternative using ALPHA_N formula to create a single string

On another page I have a formula that will create a single string for sorting with the alpha portion on left, the numeric portion on the right and sufficient zeroes in between to fill out the string to the specified number of characters.  See Sorting strings with alpha on left and digits on right

Regular Expressions to help with sorting (#regexpr)

Extraction of a Group of Digits and Dashes, from postings by Harlan Grove, Regular Expressions.

You can generate a sort macro by recording a macro to see what is generated. 

• Tools, Macros, Record macro, accept the macroname suggested and remember it (write it down)
• Ctrl+A   -- to select ALL cells (all rows, all columns, all cells)
• Data, Sort
• Sort Column A, Ascending, Column B, Ascending
• Stop Recording (use the black Stop button), or Tools, Macro stop recording if you lost your button.
• To see your macro:  Alt+F11, Ctr+R (View Project), Select your workbook, i.e. VBAProject (myworkbook.xls), look at Module 1 in your project library (myworkbook.xls), look for your macro i.e. Macro 1

You will see something like this:

Sub Macro39()
    Cells.Select
    Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("B2") _
        , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
        False, Orientation:=xlTopToBottom
End Sub

Recording a Macro hardly ever gets you exactly what you want.  Generally you use it to get an idea of what statements you need to use, and then look at those commands (methods) in your VBE HELP.  In this case the coding generated is pretty close to what you want.  So only very minor modifications were necessary.

Sub SortA_then_B()
    Cells.Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("B2"), _
        Order2:=xlAscending, Header:=xlYes, OrderCustom:=1, _
        MatchCase:=False, Orientation:=xlTopToBottom
End Sub

Reminder, if not familiar with installing and using macros see Getting Started with Macros and User Defined Functions

You can assign a shortcut key to the macro, you can assign it to a toolbar button, or to a shape on the worksheet.

Don't put all your macros in one module as it will use up a lot of memory that way.  Try to put macros that are likely to be used together in the same module.  You can rename your modules (F4) so you can tell where you got them i.e. (McRitchie_Sorting), which is of more importance in your personal.xls file with a lot of modules than in your current workbook.

Sorting triggered by an Event Macro (#activate)

Event macros are triggered by an Event and are installed differently from regular macros.  These Worksheet Event macros apply only to the sheet they are installed in. The sort will be invoked upon sheet activation and on double click by use of Event macros. As coded below the macro will sort the rows between the top row (header row), and the last row that has something in column A.  The technique to lookup from the bottom is also used on one of my toolbar buttons       personal.xls!GotoBottomofCurrentColumn     (see my macros for my toolbar buttons) To install these worksheet event macros. Right click on the sheetname, View code, insert the following:

A B C D E F  1  Who  -B-  -C-  -D-  Qty  -F-  2  Guy 2  1   30   14   6   39   3  Guy 4  44   14   94   8   77   4  Guy 1  23   67   58   9   16   5  Guy 3  65   56   68   96   64   6  Totals   133   167   234   119   196

Private Sub Worksheet_Activate()
  Dim LRow As Long    '-- SORT on Col E then A
  'Find row before last row in Column A with content
   LRow = Cells(Rows.Count, 1).End(xlUp).Offset(-1, 0).Row
   Rows("2:" & LRow).Sort Key1:=Range("E2"), _
      Order1:=xlAscending, Key2:=Range("A2"), _
      Order2:=xlAscending, Header:=xlNo, _
      OrderCustom:=1, MatchCase:=False, _
      Orientation:=xlTopToBottom
End Sub
 
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
      Cancel As Boolean)
  Cancel = True   'get out of edit mode from DoubleClick
  Worksheet_Activate
End Sub

Sorting Formatted cells (#borders)

Borders are left where they are but other cell formatting remains with the cells when sorted.  The initial example on left can be restored by selecting any cell in col 1, Ctrl+A, sort A-Z; the after by selecting any cell in col E, Ctrl+A, sort A-Z.

Sorting messed up cell references on other sheets(#vlookup)

Sorting data will not change cell references, so you might want to consider using VLOOKUP with the “exact match” option so that you can still reference the same data from another sheet to a changed location. 

Sorting messed up, where's that backup (#messedup)

Problem:
Sorted and can't put file back to how it was.

Comment:
For next time, if you won't be able to sort file back a few choices include:  1). Create another column that can be sorted, a series of numbers from 1 up created with the fill handles -- incrementing previous cell will do no good.  2). Create a duplicate spreadsheet using  Edit->Copy Sheet and sort it instead.  3). It would be wise to create backups, one form of backups would be to copy using the DOS  XCOPY command; and of course, you should create a real backup and store it at some other location in case of real disaster.

Sorting as if EBCDIC as on a mainframe (#ebcdic)

Problem:
How to sort with the appearance of mainframe sorting which uses EBCDIC characters.

Comment: See table at top which describes sorting of characters in Excel, also see my symbols page for an ascii and an historic ebcdic table (with BCD codes).  The following Function is not intended to handle all characters just the alphabet, numbers, equal sign(=), minus sign(-), and forward slash(/).  To use create a column next to source column and use formula such as   =SortBCD(A1)   then sort data on the new column.  Once sorted remove the new column which is nonsensible gibberish used to help sorting and is no longer useful once sorted.  If you make changes to the formula don't forget to hit F9 for recalculation before resorting. Unlike most other languages I've worked with Excel and VBA do not provide a TRANSLATE instruction.  In COBOL it would be TRANSFORM, others are more obvious like XLAT, XR, XRT.  In Excel the closest thing is a single character substitution using REPLACE or SUBSTITUTE, the SORTBCD macro has to to it's own character by character replacement due to a lack of builtins.

This column appears in simulated EBCDIC order because this column was created using   =SortBCD(A1) and then sorted on this column. - $ = ) A-B= 0$1) ABC0158 012QRVY AN509-10 0DVQZ$RQ AN509C10R7 0DVQZ2RQHX A39539-10-001 0TZVTZ$RQ$QQR CC 22 jj 99 JJ3 99T ZB45-37C P1UV$TX2 10-60732-3 RQ$WQXTS$T 100001 RQQQQR 11 RR 11230 RRSTQ 65-2716-2 WV$SXRW$S 6553 WVVT

Function SortBCD(aaa)
   'David McRitchie  1999-04-07
   '  see sorting in http://www.mvps.org/dmcritchie/excel.htm
   'Sort letters before numbers as in BCD and in EBCDIC.
   'FromSTR is comprised of characters to be sorted must in EBCDIC sequence
   'See http://www.mvps.org/dmcritchie/rexx/htm/symbols.htm    
   FromSTR = " -/=ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789"
   'Sortstr any characters but must be in EXCEL sequence
   'Please note EXCEL sequence differs from ASCII
   'Must be at least as long as FromSTR
   SortSTR = "#$()0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ"
   L = Len(aaa)
   capsaaa = UCase(aaa)   'bonus: will treat LCase same as UCase
   For i = 1 To Len(capsaaa)
       For j = 1 To Len(FromSTR)
          If Mid(capsaaa, i, 1) = Mid(FromSTR, j, 1) Then
             SortBCD = SortBCD & Mid(SortSTR, j, 1)
             GoTo nextI
          End If
       Next j
nextI:
   Next i
End Function

Sorting on Street Name, then House Number (#street)

Use a helper column to put house number after the streetname.

=IF(LEN(A14)=0,"",MID(A14,FIND(" ", A14&" ")+1,99)) & RIGHT("00000" & LEFT(A14,FIND(" ",A14&" ")-1),5)

Also see example using REPT Worksheet Function on join.htm page.

Sorting Titles aphabetically with a helper column (#titles)

To remove “The ” as the first word in a helper column to be sorted
   A2:   'The Cat and the Cradle
   B2:   =IF(LEFT(UPPER(A2),4) = "THE ",MID(A2,5,200),A2)

Sorting letter codes non alphabetically (#codeletter)

Use a helper column to assign a digit to the code letter (a=active (1), w=work in progress(2), f=finshed(3)).  The formula below assigns single digit code of 1, 2, or 3 not semi-colon is the list separator.  For an excample of a 2 column array contained in a VLOOKUP formula see gradebook example on VLOOKUP page.

 =MATCH(B1,{"a";"w";"f"},0)

Show a different number

Poster wanted to show 1,2,3 instead of 4,8,12 nothing else is supposed to be present.  David R., whoever that is, answered 2004-05-05.
 =CHOOSE(A1/4,1,2,3)

If you don't want any message if not 4,8,12, try
  =IF(ISNUMBER(CHOOSE(A1/4,1,2,3)),CHOOSE(A1/4,1,2,3),"")

Some Specialized Sorting (#special)

Sorting a range by Columns

Tom Ogilvy's solution will sort all data for each column separately for columns A through G regardless of whether range is A2:G100, A3:G4 Both examples, use default Header:=xlNo Sub sortEachColumn() 'Tom Ogilvy, 2001-03-24, Programming Dim col As Range For Each col In Range("a2:g100").Columns col.Sort key1:=col, Order1:=xlAscending Next End Sub

Just for comparison and a more literal interpretation: This variation will only affect data within the selection area, sorting each column individually.  Sub sortEachColumn2() Dim col As Range ' --- Range("a2:g100").Select For Each col In Selection.Columns Intersect(col, Selection, _ ActiveSheet.UsedRange). _ Sort key1:=col, Order1:=xlAscending Next End Sub

Sorting a selection by Rows, with each Row being independent of the other rows

The following is based on Tom Ogilvy's example but has been made a bit more generic to process a Selection instead of a specific Range.  Example shows a single selection area, modified to distinguish rows.

Sub sortEachRow() 'based on Tom Ogilvy, 2001-03-24, Programming Dim rw As Range If Selection.Columns.Count = 1 Then MsgBox "your selection must involve more" _ & " than one cell or column" Exit Sub End If For Each rw In Selection.Rows rw.Sort key1:=rw, Order1:=xlAscending, _ Header:=xlNo, OrderCustom:=1, _ MatchCase:=False, _ Orientation:=xlLeftToRight Next End Sub

A B C D E F  1  98   76   54   79   39   99   2  73   87   54   67   58   87   3  10   58   32   48   4   62   4  82   37   47   30   10   78   5  69   53   3   46   54   60   6  24   78   95   49   38   13   7  83   37   54   56   5   33   8  95   69   88   46   94   81   9  49   6   17   29   78   19  10  3   25   51   40   39   40

A B C D E F  1  98   76   54   79   39   99   2  73   54   58   67   87   87   3  10   4   32   48   58   62   4  82   10   30   37   47   78   5  69   3   46   53   54   60   6  24   38   49   78   95   13   7  83   5   37   54   56   33   8  95   46   69   88   94   81   9  49   6   17   29   78   19  10  3   25   51   40   39   40

A more generalized version (#normdigits)

In this more generalized function, the numbers will be normalized at 3 digits, which is also the default.  All non digits will be passed through directly.

NormDigits   Example can also be used for TCP/IP addresses (#normdigits)

1.1.1.1	001.001.001.001	=personal.xls!NormDigits(A1)
31.32.34.250	031.032.034.250	=personal.xls!NormDigits(A2)

ChemNDigits   Example with Chemical Nomenclature (#ChemNDigits)

CHNO  C-001H-001N-001O-001  =personal.xls!ChemNDigits(A11)  C3H2  C-003H-002  =personal.xls!ChemNDigits(A12)  C6H12NO2  C-006H-012N-001O-002  =personal.xls!ChemNDigits(A13)  C10H5F3  C-010H-005F-003  =personal.xls!ChemNDigits(A14)  C12H6FO2  C-012H-006F-001O-002  =personal.xls!ChemNDigits(A15)  C12H6F3  C-012H-006F-003  =personal.xls!ChemNDigits(A16)  Cs4H20  Cs004H-020  =personal.xls!ChemNDigits(A17)

For code and more examples including sorting on chemical nomenclature see Sort TCP/IP page.
To print Cs4H20 as Cs₄H₂₀ you can use a subroutine posted by David Hagar, programming, 2002-01-22

For a simpler way of entering subscript and superscripts than provided in Excel see J-Walk SuperSub,

Wide range of answers to misunderstood question (#interesting)

A non explicit request from Hans Knudsen (Google Usenet Archives 464836683) on 1999-04-10 produced several answers.  What was really wanted was to sort every single cell in the range down one column and continue down the next within range.

I have data in cells A1:E35, and seek the easiest way to sort these data ascending?

• David McRitchie -- to sort range on Column A of range. (specific to range)
• Iwer Mørck -- to sort on each of 5 columns independently.  (specific to range)
• PhilCxn -- to sort entire range down one column then continue down the next. (specific to range)
• PhilCxn -- a faster version that handles numbers only. (specific to range)
• Leo heuser -- a generalized version that allows to overwrite or place somewhere else, to sort entire range down columns continue next, or across rows continue next.  Leo warns that formulas will be lost -- that would be expected.  Operational note: The last column on the sheet 256 (IV1) is used as a buffer.  (nonspecific to range)  Also for xl95 change “worksheetfunction.counta” to “counta”

  Leo repeated part (3) of a more comprehensive posting from Dec 1998 presented as a Christmas present.

  1. Transferring a n*m range to a n*1 or 1*m range.
  2. Transferring a n*m range to a m*n range
  3. Sorting a range showing the result by rows or by columns.  The result is placed in the original range or elsewhere on the sheet.

  The David Braden and Myrna Larson post includes 4095 in the body so I include another post from Leo where he notes that: In Excel 5.0 the maximum number of elements to tranfer from the sheet to an array is 4095.
  For Excel 7.0 (Excel 95) the corresponding number is 5461.
  In Excel 8.0 (Excel 97) the limit is the size of a worksheet i.e. 65536 * 256, so the only solution is to upgrade to Excel 97.
• David Braden and Myrna Larson -- three more sorting solutions.  (The two macros are nonspecific range, the array solution is specific to range).
• Steve Bullen has a QuckSort.zip download that implements a "Quicksort" algorithm in VBA which he describes as very fast and handles both 1D and 2D arrays.

Selection of a current region (Ctrl+ SHIFT+ * ) may occur automatically when a sort is invoked.  Leo's solution is coded to perform in a similar fashion to use the current region when only a single cell was selected.

Problems   (#problems)

• Incorrect selection area prior to sorting (Selecting Region).
• Not understanding the collating sequence used by sort and the fact that numbers are sorted before text.  Basically numbers before text, and digits before letters.
• Merged cells which have to be unmerged before sorting.

Related Information - Sorting, Collating Sequence (#related)

• Backup your data, sooner or later you will lose a file or a hard drive and want to restore it, but you need a backup in order to restore from one.  Backup often so you have recent backups.  Besides restoring files, I've had to restore my hard drive after getting a larger HD, and once again when the replacement broke.
• Choosing Region to be sorted
• ExtractDigits, User Defined Function, extract first set of consecutive digits from a string.  Treat as a number if found as an empty string if none found (i.e., g25abc16 would return 25).  In the next posting in same thread is a means to extract set of digits from left/middle/right using an array formula, but it fails if you have two sets of digits in the same string (i.e. g25abc16 would fail).
• Extraction of a Group of Digits and Dashes, from postings by Harlan Grove
• Fill Empty, includes manual steps, and macro solutions to fill in empty cells below descriptive headers, so a table can be sorted.
• HELP information as seen in Excel XP HELP
• Sorting Movie Titles, Jim Cone, addin, sorts without leading articles (a, and, the) changing numbers at beginning to words.
• Sorting Sheets in Workbook and macros to sort and list sheets found in workbook.  Also notes at end of pathname), MS KB article Q105069 and Enumerate addins in My Site Index.
• SortAllSheets, sort sheet tabs, sort worksheet tabs
• Sorting a MultiColumn ListBox, Dick Kusleika
• Sorting an Outline format, Peter Huang [MSFT], programming, 2003-09-15.
• Sorting on a Value That Appears in Alternate Rows
• Sorting strings with alpha on left and digits on right
• Sorting TCP/IP Addresses and the likes of Outline numbers, normalized digits, Chemical Nomenclature, domain name
• Symbols contains ASCII and EBCDIC tables.
• TRIMALL macro to convert Char(160) and TRIM spaces, which may interfere with sorting.

Visit [my Excel home page]   [Index page]   [Excel Onsite Search]   [top of this page]

Please send your comments concerning this web page to: David McRitchie send email comments

Copyright © 1997 - 2004,  F. David McRitchie,  All Rights Reserved