Sorting section numbers doesn't give correct order

[facmanboss]

I have procedures where I track each section in a spreadsheet along with
requirements for the section. One column has the section numbers. Here is an
example to show the problem. Each number is in a seperate cell in the column.
I used semicolons here to seperate the numbers on the same line for posting
clarity. The cells are formatted as "text".

unsorted (also correct order): 1.3.2.1; 1.3.2.2; 1.3.2.3; 1.3.2.10; 1.3.2.11

sort ascending (incorrect order): 1.3.2.1; 1.3.2.10; 1.3.2.11; 1.3.2.2;
1.3.2.3

I cannot add leading zeroes manually such as 1.3.2.1 -> 1.3.2.01.
Nor can I create a special column to manipulate these.
They are dynamic, thousands of rows long, and change constantly. hence you
see the need for proper sorting , easily.

VBA may or may not be able to be used in this application due to security
restrictions. But I'll entertain VBA solutions.

Thanks for any suggestions.
Facmanboss

 

[Niek Otten]

<Nor can I create a special column to manipulate these>

Why not? You can hide the column if you don't want to see it.

 

[Fred Smith]

If this were me, I would create a helper column, which defines the sort
order.

In your case, if you always have 4 sections as shown, the formula would be
pretty straightforward:
=left(a1,1)*10000+mid(a1,3,1)*1000+mid(a1,5,1)*100+right(a1,len(a1)-6)

Even if all sections have a variable number of digits, you can still devise
a formula. Now sort by the helper column. If you don't want to see it, hide
it.

If you insist on not having a helper column, use VBA. Create the sort
column, sort, then delete the column.

Regards,
Fred.

 

[Jim Cone]

My "Special Sort" Excel add-in has two dozen ways to sort not available
in Excel. It will do the decimal sort you need.
The trial version of the program is free and available to those who email
and request it. Remove xxx from my email address...

james.coneXXX at comcast.comXXX
Please include your real name and geographic location.
--
Jim Cone
Portland, Oregon USA



"facmanboss"
<[email protected]>
wrote in message
I have procedures where I track each section in a spreadsheet along with
requirements for the section. One column has the section numbers. Here is an
example to show the problem. Each number is in a seperate cell in the column.
I used semicolons here to seperate the numbers on the same line for posting
clarity. The cells are formatted as "text".
unsorted (also correct order): 1.3.2.1; 1.3.2.2; 1.3.2.3; 1.3.2.10; 1.3.2.11

sort ascending (incorrect order): 1.3.2.1; 1.3.2.10; 1.3.2.11; 1.3.2.2;
1.3.2.3

I cannot add leading zeroes manually such as 1.3.2.1 -> 1.3.2.01.
Nor can I create a special column to manipulate these.
They are dynamic, thousands of rows long, and change constantly. hence you
see the need for proper sorting , easily.
VBA may or may not be able to be used in this application due to security
restrictions. But I'll entertain VBA solutions.
Thanks for any suggestions.
Facmanboss