Sorting numbers

billfan

New Member
Joined
Aug 2, 2004
Messages
37
I'm trying to sort a list of codes I have in one column in excel, about 100 rows worth. The problem that I'm having is that I want to sort them in overall increasing chronological order, but Excel keeps putting the codes with letters at the end of them in their own order at the end, after all the "number only" codes are done being sorted. They look like this

09879
11334
14589
34875
36527
54091
54283
75893
99211
23143T
36345F
38456F
59840B
59840D

But I want them to sort like this:
14589
23143T
34875
36345F
36527
38456F
54091
54283
59840B
59840D
75893
99211

I'm using AutoFilter, then the Data-->Sort function, but it keeps sorting them the first way. I need it the second way. Any thoughts? Thanks in advance!
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

hatman

Well-known Member
Joined
Apr 8, 2005
Messages
2,664
Check out this post of mine... it only works for the first 8 characters of a text string, which was less than ideal for me... and is NOT case sensitive (mainly because this needs to use 2 digit Ascii codes, and the lower case letters extend beyond 100) but I think it'll work for you.

http://www.mrexcel.com/board2/viewtopic.php?t=234858
 

hatman

Well-known Member
Joined
Apr 8, 2005
Messages
2,664
Here is my ranking tool modified for your application... I had to force all strings to be the same length. Note that your sorted list from above is in column AQ for comparison... minus the discrepency that jindo pointed out, this seems to work.
Book1
AJAKALAMANAOAPAQ
169879575655573231311334
2113344949515152311458914589
31458949525356573223143T23143T
4348755152565553343487534875
53652751545350553636345F36345F
6540915352485749383652736527
75428353525056513938456F38456F
87589355535657513125409154091
99921157575049493145428354283
1023143T50514952518359840B59840B
1136345F51545152537559840D59840D
1238456F5156525354777589375893
1359840B53575652487109879
1459840D53575652487119921199211
Sheet1
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832

ADVERTISEMENT

Hi,

perhaps use a helpercolumn
="&" & A1
then sort both columns together and use other column as key

   A       B        
 1 09879   &09879   
 2 11334   &11334   
 3 14589   &14589   
 4 23143T  &23143T  
 5 34875   &34875   
 6 36345F  &36345F  
 7 36527   &36527   
 8 38456F  &38456F  
 9 54091   &54091   
10 54283   &54283   
11 59840B  &59840B  
12 59840D  &59840D  
13 75893   &75893   
14 99211   &99211   

Blad1

[Table-It] version 06 by Erik Van Geit
Code:
RANGE   FORMULA (1st cell)
B1:B14  ="&" & A1

[Table-It] version 06 by Erik Van Geit

kind regards,
Erik
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,784
Office Version
  1. 365
Platform
  1. Windows
billfan

I am interested in the answer to jindon's question but assuming you accidentally left them out of your solution, this may be of use. It is based on all the 'number' parts of your codes being 5 characters long as in the samples provided.

1. In B1 (copied down): =LEFT(A1,5)
2. Now sort columns A and B using column B as the sort key

This is my result after the sort:
Mr Excel.xls
ABCD
10987909879
21133411334
31458914589
423143T23143
53487534875
636345F36345
73652736527
838456F38456
95409154091
105428354283
1159840B59840
1259840D59840
137589375893
149921199211
15
Sort
 

hatman

Well-known Member
Joined
Apr 8, 2005
Messages
2,664

ADVERTISEMENT

perhaps use a helpercolumn
="&" & A1
then sort both columns together and use other column as key

Good point Erik... it took me a second to realize what you were doing differently from my first attempt, which didn't work... that was because I was trying to turn these strings into text using the Text() function and/or leading/trailing spaces... but it was too late last night for me to realize that those methods were too subtle to prevent Excel from looking at number-like strings as numerals.

Someday I may be able to find simple solutions :wink: Until then, I'll just keep making things more complicate dthan they have to be :cool:
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,784
Office Version
  1. 365
Platform
  1. Windows
perhaps use a helpercolumn
="&" & A1
Erik, is there a reason for
="&" & A1
rather than, say, just
="" & A1
 

hatman

Well-known Member
Joined
Apr 8, 2005
Messages
2,664
Yup... try it. If you simply concatenate a space or a logical blank to turn the numbers into text, Excel STILL insists on interpretting those number-like strings as numerals, and you haven't made any gains... by concatenating some text character (could be anything, as long as it is the same for everything) it defeats Excel's insistance on second-guessing that it's a numeral because it looks like one, even though it's reallly text.

Edit: I did the same double take... which prompted my previous post :biggrin:
 

Watch MrExcel Video

Forum statistics

Threads
1,112,880
Messages
5,543,007
Members
410,583
Latest member
gazz57
Top