Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
Trudy
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
Sloth
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
Trudy
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
Sloth
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
Jim Cone
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
Ron Rosenfeld
 
Posts: n/a
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Sorting hyphenated numbers Connie Martin Excel Discussion (Misc queries) 5 February 20th 09 01:57 PM
Trouble Sorting Averages of Randomly Generated Numbers GStrawley Excel Discussion (Misc queries) 3 September 5th 05 10:39 PM
Sorting when some numbers have a text suffix confused on the tundra Excel Discussion (Misc queries) 5 December 18th 04 11:19 PM
Sorting imported "numbers" Confused on the tundra Excel Discussion (Misc queries) 5 December 17th 04 08:33 PM
Sorting numbers Douglas Sey New Users to Excel 0 November 27th 04 09:54 PM


All times are GMT +1. The time now is 01:27 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"