Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting numbers with differing numbers of digits
We have a list of numbers that we are having difficulty sorting. For
example, 7000-7100 should come after 700-710 because 7000 is more than 700. The text ABC-D and a space should also preceed each number. Can you please assist? Thanks. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting numbers with differing numbers of digits
Look in the help under "Default sort orders" to gain a better understanding
of how Excel sorts data. I don't think you can change the sorting rules. You just have to learn to work around them. For example: If you want 700-710 to come before 7000-7100 you will need to enter it as 0700-0710. "Trudy" wrote: We have a list of numbers that we are having difficulty sorting. For example, 7000-7100 should come after 700-710 because 7000 is more than 700. The text ABC-D and a space should also preceed each number. Can you please assist? Thanks. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting numbers with differing numbers of digits
Thank you! Our problem is that our client will not allow leading zeros. I
was hoping to find a custom format that would solve this dilema. "Sloth" wrote: Look in the help under "Default sort orders" to gain a better understanding of how Excel sorts data. I don't think you can change the sorting rules. You just have to learn to work around them. For example: If you want 700-710 to come before 7000-7100 you will need to enter it as 0700-0710. "Trudy" wrote: We have a list of numbers that we are having difficulty sorting. For example, 7000-7100 should come after 700-710 because 7000 is more than 700. The text ABC-D and a space should also preceed each number. Can you please assist? Thanks. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting numbers with differing numbers of digits
I suppose you could use a helper column, and sort according to that column.
You could use a formula like this to convert 700-710 to 0700-0710. You could then hide the helper column. =TEXT(LEFT(A1,FIND("-",A1)-1),"0000")&"-"&TEXT(RIGHT(A1,LEN(A1)-FIND("-",A1)),"0000") "Trudy" wrote: Thank you! Our problem is that our client will not allow leading zeros. I was hoping to find a custom format that would solve this dilema. "Sloth" wrote: Look in the help under "Default sort orders" to gain a better understanding of how Excel sorts data. I don't think you can change the sorting rules. You just have to learn to work around them. For example: If you want 700-710 to come before 7000-7100 you will need to enter it as 0700-0710. "Trudy" wrote: We have a list of numbers that we are having difficulty sorting. For example, 7000-7100 should come after 700-710 because 7000 is more than 700. The text ABC-D and a space should also preceed each number. Can you please assist? Thanks. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting numbers with differing numbers of digits
Trudy,
A commercial application by yours truly... http://www.officeletter.com/blink/specialsort.html Jim Cone San Francisco, USA |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting numbers with differing numbers of digits
On Wed, 1 Mar 2006 14:59:28 -0800, "Trudy"
wrote: We have a list of numbers that we are having difficulty sorting. For example, 7000-7100 should come after 700-710 because 7000 is more than 700. The text ABC-D and a space should also preceed each number. Can you please assist? Thanks. You will need to modify the data, but you can do it simply with worksheet formulas. With your original data in A2:An Assume you will display column B: B1: =--SUBSTITUTE(A1,"-","") Copy/Drag down to Bn. Select B1:Bn Format/Cells/Number/Custom Type: [1000000]"ABC-D "0000-0000;"ABC-D "000-000 Then sort on Column B. This also assumes that your ranges are either both three digit ranges; or both four digit ranges. If there is more variability, post back. Also, one could Paste Special the Values over column B and delete column A. One could also do this with a macro if desirable. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sorting hyphenated numbers | Excel Discussion (Misc queries) | |||
Trouble Sorting Averages of Randomly Generated Numbers | Excel Discussion (Misc queries) | |||
Sorting when some numbers have a text suffix | Excel Discussion (Misc queries) | |||
Sorting imported "numbers" | Excel Discussion (Misc queries) | |||
Sorting numbers | New Users to Excel |