A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Sort list



 
 
Thread Tools Display Modes
  #1  
Old March 2nd, 2010, 12:09 PM posted to microsoft.public.excel.newusers
janey
external usenet poster
 
Posts: 36
Default Sort list

I have a table which I need to sort, e.g.:

2000
2039B
2026
2167
2239
2242

When I run Sort, I get
2000
2026
2167
2239
2242
2039B

Why does 2039B come at the bottom rather than between 2026 and 2167?
  #2  
Old March 2nd, 2010, 02:13 PM posted to microsoft.public.excel.newusers
Bill R[_5_]
external usenet poster
 
Posts: 31
Default Sort list

"janey" wrote in message
...
I have a table which I need to sort, e.g.:

2000
2039B
2026
2167
2239
2242

When I run Sort, I get
2000
2026
2167
2239
2242
2039B

Why does 2039B come at the bottom rather than between 2026 and 2167?


Because numbers are numbers and are sorted numerical. Other data is alpha
numeric which are sorted in order but after numerical data. You could try
adding a column for the alphabetical element and sort on the numerical data
(but include the alpha numerical date in the sort range) and that will be
OK.

Bill R


  #3  
Old March 2nd, 2010, 02:31 PM posted to microsoft.public.excel.newusers
Max
external usenet poster
 
Posts: 8,574
Default Sort list

"2039B" is text, and any text is treated by Excel as being greater than the
largest real number, that's why. To sort these like real numbers, use a
helper col,
eg in B1, copied down: =IF(A1="","",IF(ISNUMBER(A1),A1,LEFT(A1,4)+0))
Then sort both cols A and B by col B, ascending, and you should get the
desired sort in col A. Success? hit YES below
--
Max
Singapore
---
"janey" wrote:
I have a table which I need to sort, e.g.:

2000
2039B
2026
2167
2239
2242

When I run Sort, I get
2000
2026
2167
2239
2242
2039B

Why does 2039B come at the bottom rather than between 2026 and 2167?

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 08:45 PM.


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