VBA / Excel sorts text with numbers in wrong order

[Pete Wright]

Hi everyone!

I have an Excel workbook with data that can be sorted with a button.
The problem is, that the data contains alphanumerical data (pure numbers, numbers with text, etc.) and the default Excel sorting algorithm gives wrong results.

To keep things easy I have attached a simplified workbook with less data and only one sheet.

There are two columns: "code" and "color"
Here are some of the possible values for "code":

101, 102, 101/102, 103, 103-A, 103-B, 104, 104 X, 104 Y, 105, 106, 107

The result of the sorting looks like this:
101, 102, 103, 104, 105, 106, 107, 101/102, 103-A, 103-B, 104 X, 104 Y

What it should look like:
101, 101/102, 102, 103, 103-A, 103-B, 104, 104 X, 104 Y, 105, 106, 107


Any ideas how this can be achieved?

Many thanks in advance
Pete


___
I cannot attach the file due to a server error, so I put it on my drive:
vba_sort_text.xlsm

 

[vletm]

Pete Wright
eg with help column:
for c2 ="aa" & a2 and copy down
after those
sort Your data as needed.
Ooopas! There were some Macros too.
Seems that with both DataOptions gives same result; tested.
Above 'help column'-way could add to Your code and use 'clearcontents' after sorting.

 

[NARAYANK991]

Hi ,

Use the DataOption setting : DataOption:=xlSortNormal

Narayan

 

[pecoflyer]

The results XL proposes are not wrong
When comparing numbers for sorting, XL first checks the TYPE() value of the cell which is 1 for a number and 2 for text, etc...
XL then first sorts cell contents with TYPE value 1, then 2 , etc...
Perhaps changing your numbers to text might help

 

[Pete Wright]

Use the DataOption setting : DataOption:=xlSortNormal

Thanks! This finally worked for me.

One more question: How safe is this setting? I mean, will it work on any version and any language of Excel?
Right now I have tested it on two independent computers with different versions of Excel and it worked.

But is it safe to "spread" the file (share it in a company with many different computers / OSs / Excels?

Kind regards
Pete

 

[vletm]

Pete Wright
As I wrote in #2, no matter of DataOption, it didn't work with Mac with me.

 

[Pete Wright]

Darn!

Maybe there should be some other options or settings to define with Application object (Excel)?

 

[vletm]

Pete Wright
If You'll find, let me know ...
I would do that as I wrote ... with some help ... that will work.