Sort numbers stored as text

ajoy123rocks · Sep 13th 2021

I have 3 columns one of which contains numbers stored as text which can contain values (00, 0 to 99). Now i need to sort this column in ascending order. After sorting i need 00 on top of the row and then the remaining numbers in ascending order. When i sort using below code, if I make "0" entry before "00", the result after sort is "0" before "00". if the entry is made vice versa, I get "00" before "0". My requirement is i require 00 to be at the top in all cases after sort.

------------------------------------------------------------------------

Code

`Range("B12:H24").Select
ActiveWorkbook.Worksheets("Game_Info").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Game_Info").Sort.SortFields.Add2 Key:=Range( _
"F13:F24"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortTextAsNumbers
With ActiveWorkbook.Worksheets("Game_Info").Sort
.SetRange Range("B12:H24")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With`

Display More

**royUK** · Sep 13th 2021

Welcome to the Forum. Please read the Forum Rules to understand how the Forum works and why I have added Code Tags to your post

All VBA code posted in the forum must be wrapped in code tags, which you omitted, including single-line code snippets.Be sure to use them in future posts.

How to use code tags

Just highlight all of the code and press the <> in the post menu above button to add the code tags.

Thanks.

**royUK** · Sep 13th 2021

You need to convert them to numbers but 0 is the same as 00

ajoy123rocks · Sep 14th 2021

i need both 0 and 00 in the list. both are different as per my requirement

ajoy123rocks · Sep 16th 2021

Any help is much appreciated.

**KjBox** · Sep 16th 2021

When sorting "00" is treated the same as "0". Assuming you are using whole numbers only then a work-around is to first change the "0"s to "0.1"s, then sort, then change the "0.1"s back to "0"s.

Try replacing the code you have with this

Code

    With Sheets("Game_Info")
        .Range("F13:F24").Replace "0", "0.1"
        With .Sort
            .SortFields.Clear
            .SortFields.Add2 .Parent.Range("F13:F23"), _
             0, 1, DataOption:=1
            .SetRange .Parent.Range("B12:H24")
            .Header = 1: .Apply
        End With
        .Range("F13:F24").Replace "0.1", "0"
    End With

Display More

ajoy123rocks · Sep 17th 2021

the code is setting "0" as "0.1" and "00" as "0.10.1" and on replacing back it is setting both to "0".

**KjBox** · Sep 17th 2021

Ok I will check on that later when I get home

Can you attach a sample file

ajoy123rocks · Sep 17th 2021

PFA sample file

VincentNL · Sep 17th 2021

I understand it correctly you want it sorted like this?

ajoy123rocks · Sep 17th 2021

exactly

**rory** · Sep 17th 2021

In KjBox's code, change this:

Code

.Range("F13:F24").Replace "0", "0.1"

to this:

Code

.Range("F13:F24").Replace "0", "0.1", xlWhole

VincentNL · Sep 17th 2021

Quote from ajoy123rocks

exactly

I can't think of anything else than this pretty cheap solution:

Code

Sub MacroSort()
ws = "Sheet1" 'Change "sheet1" to your sheet name
Columnnumber = 5 'Change 5 to the number of the column where the numbers are in
Worksheets(ws).Activate
    With ActiveSheet
    lastrow = .Cells(.Rows.Count, "E").End(xlUp).Row 'Change "E" to column that contains the numbers
    End With

Worksheets(ws).Sort.SortFields.Clear
Worksheets(ws).Sort.SortFields.Add2 Key:=Range(Cells(2, Columnnumber), Cells(lastrow, Columnnumber)) _
        , SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:= _
        "00,0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100" _
        , DataOption:=xlSortTextAsNumbers
    With ActiveWorkbook.Worksheets(ws).Sort
        .SetRange Range(Cells(1, 1), Cells(lastrow, Columnnumber))
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    


End Sub

Display More

ajoy123rocks · Sep 17th 2021

Thanks a milllion @KjBox and @rory

**KjBox** · Sep 17th 2021

Thanks Rory, I just got back and was about to post the same solution.

Sort numbers stored as text

Participate now!

Share

Tags