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