Excel: How can I sort a range of numbers with leading zeros as a pivot table does?

Question

I need to sort a range of numbers on a sheet to match the sort done by a second, very similar set of data in a pivot table. The numbers are in string format as some of them start with 0, 00 etc.

Removing the leading zeros is not an option as this would duplicate entries (where I have 056 and 0056, for example).

The strings are of different lengths and not just because of the leading zeros.

The pivot table sorts like this, by default (it seems to sort the numbers only as strings):

Basically I need the order to be like:

00056
00182
01097
100
106
10610
109
86930
870032
87035
...

Things I have tried:

1. Sorting on the sheet anything that looks like a number as a number;
2. Sorting numbers and text separately;
3. Sorting by a custom list where the custom list is the set of data in the pivot table.

It seems that you can't force Excel to sort a range of numbers solely as strings, where as it seems like a pivot table has no problem doing this.

I'd prefer not to have to also pivot the first set of data I have just to sort it as a pivot table would!

Any ideas?

Thanks in advance,

P.S. I'm really only looking for an answer to this question, not solutions to different problems or questions as to why I'm doing this in the first place, thanks!

Answer 1

Insert a new column and pad trailing zeroes to an equal length for all values. Sort with that column as the primary key then delete the column.

Option Explicit


Sub funnySort()

    With Worksheets("sheet2")
        .Columns("A").Insert
        .Range(.Cells(2, "A"), .Cells(.Rows.Count, "B").End(xlUp).Offset(0, -1)).Formula = _
            "=LEFT(B2&REPT(0, 9), 10)"
        With .Range(.Cells(1, "A"), .Cells(.Rows.Count, "B").End(xlUp))
            .Sort Key1:=.Cells(1), Order1:=xlAscending, _
                  Orientation:=xlTopToBottom, Header:=xlYes
        End With
        .Columns("A").Delete
    End With

End Sub