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.

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.

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.

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.

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

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