Sorting numbers

  • Thread starter Thread starter wavers3
  • Start date Start date
W

wavers3

I am trying to sort numbers from the left. Example would be that ALL the 1's
are at the top, the the 2's and so on ending with the 9's. No matter how long
the length is.
It is not sort smallest to largest or largest to smallest. It would look
something like this:
1
12
123
2
21
213
3
31
312
4
41
412
 
Probably the easiest will be to convert the numbers to text and then sort
that column.

In an adjacent column add the formula
=text(A1, "#")
and copy down

This creates a list of your numbers but stored as text. Sort this column. If
/ When asked how you would like it sorted select Numbers and text Seperately.
 
The numbers must be text to sort as your example.

Run this macro on the column then sort.

Sub Add_Text()
Dim Cell As Range
Dim moretext As String
Dim thisrng As Range
Set thisrng = Selection
moretext = "'"
For Each Cell In thisrng
Cell.Value = moretext & Cell.Value
Next
End Sub

When you sort ascending you will be given two options.

1. Sort anything that looks like a number as a number.

2. Sort numbers and numbers stored as text separately.

Select option 2.


Gord Dibben MS Excel MVP
 
Back
Top