Sorting numbers with differing numbers of digits

[Guest]

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.

 

[Guest]

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.

 

[Guest]

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.

 

[Guest]

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")

 

[Jim Cone]

Trudy,
A commercial application by yours truly...
http://www.officeletter.com/blink/specialsort.html
Jim Cone
San Francisco, USA

 

[Ron Rosenfeld]

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