How? Sort Custom Numbering Scheme

[facmanboss]

I cannot sort properly a document numbering scheme. Currently using Excel
2007. The data is formatted as text. I keep procedure section numbers in one
column and details about each section in a seperate column. A third column
and further columns keeps cross-referenxce information. The spreadsheet may
be thousands of rows long. Details about the sections constantly change or
the data needs to be presented in different ways. Therefore, I need to
constantly sort. Filtering is no problem.

Here is an example of the numbering scheme that will not sort properly.
Sorting should be left-to-right between each decimal point. In Excel, each
number is on it's own row in the same column. The scheme may also have
letters rather than numbers or letters combined with numbers (e.g., 10a, 10b)
in any position.

Desired Excel Ascend. Sort
2.2.1 2.2.1
2.2.1a 2.2.1a
2.2.3 2.2.3
2.2.4 2.2.4
2.3.1.1 2.3.1.1
2.3.1.1a 2.3.1.10
2.3.1.2 2.3.1.11
2.3.1.2a 2.3.1.1a
2.3.1.2d 2.3.1.2
2.3.1.3 2.3.1.2a
2.3.1.10 2.3.1.2d
2.3.1.11 2.3.1.3
2.3.1a.3 2.3.10.1
2.3.1a.3a 2.3.10.12
2.3.1b.3.1a 2.3.10.13
2.3.10.1 2.3.10.2
2.3.10.2 2.3.10.3
2.3.10.3 2.3.1a.3
2.3.10.12 2.3.1a.3a
2.3.10.13 2.3.1b.3.1a

I cannot add leading zeroes to the numbers. (e.g., 2.3.1.1 -> 02.03.01.01).
Numbering scheme does not allow. I cannot manually create an artificial sort
column that massages the numbers to sort properly as the sheet data
constantly changes and is thousands of rows long. VBA may be used as a last
resort.

Any help appreciated.
Facmanboss

 

[Mike H]

Hi,

You need helper column. Assuming your numbers are in A1 down put this in B1

=MID(SUMPRODUCT(--MID("01"&A1,SMALL((ROW($A$1:$A$300)-1)*ISNUMBER(-MID("01"&A1,ROW($A$1:$A$300),1)),ROW($A$1:$A$300))+1,1),10^(300-ROW($A$1:$A$300))),2,300)

Enter the formula as an array (see below) and double click the fill handle
to fill the formula down. Select both columns and sort ascending on the
helper column.

This is an array formula which must be entered with CTRL+Shift+Enter and NOT
'just enter. If you do it correctly then Excel will put curly brackets around
'the formula{}. You can't type these yourself. If you Edit the ranges
'then you must re-enter as An array.

Credit to Lars-Ã…ke Aspelin for the formula to extract the numbers.

Mike

 

[Mike H]

Hmmm,

I never checked carefully enough, doesn't work.

Mike

 

[Fred Smith]

I suggest you go with your last resort.

Regards,
Fred.