Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting numbers
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting numbers
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. -- HTH... Jim Thomlinson "wavers3" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting numbers
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 On Tue, 2 Jun 2009 13:51:00 -0700, wavers3 wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting numbers
Hello,
I suggest to use my UDF GSort with String sort: http://www.sulprobil.com/html/sort_vba.html Regards, Bernd |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting numbers
Jim,
Thanks, that did what I needed it to do. Appreciate the help, from every one. "Jim Thomlinson" wrote: 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. -- HTH... Jim Thomlinson "wavers3" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I keep my numbers while sorting? | Excel Discussion (Misc queries) | |||
Sorting high numbers from low numbers between two rows | New Users to Excel | |||
Sorting - cells containing numbers, numbers and letters | Excel Discussion (Misc queries) | |||
Sorting numbers with differing numbers of digits | Excel Discussion (Misc queries) | |||
sorting the row by numbers | Excel Worksheet Functions |