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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
sorting numbers in a column
Problem: The numbers I am sorting are composed of various numbers of integers, some having four digits, others five or six, and so on. I would like to have them sort by number regardless of number of digits. Will Excel let me do this? example: presently it looks like this: 3004 12001656 530393505 2200498378 and I really want it to sort like this: 12001656 2200498378 3004 530393505 Can this be done? -- kailuamike ------------------------------------------------------------------------ kailuamike's Profile: http://www.excelforum.com/member.php...o&userid=29558 View this thread: http://www.excelforum.com/showthread...hreadid=494495 |
#2
|
|||
|
|||
sorting numbers in a column
One way is to use a helper col to convert the col of numbers to text,
then sort both cols by the helper col Assume the numbers are in A1 down Put in B1, copy down: =TEXT(A1,"@") Now sort both cols A & B by col B, ascending -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "kailuamike" wrote in message ... Problem: The numbers I am sorting are composed of various numbers of integers, some having four digits, others five or six, and so on. I would like to have them sort by number regardless of number of digits. Will Excel let me do this? example: presently it looks like this: 3004 12001656 530393505 2200498378 and I really want it to sort like this: 12001656 2200498378 3004 530393505 Can this be done? -- kailuamike ------------------------------------------------------------------------ kailuamike's Profile: http://www.excelforum.com/member.php...o&userid=29558 View this thread: http://www.excelforum.com/showthread...hreadid=494495 |
#3
|
|||
|
|||
sorting numbers in a column
Hi Kailua,
A user defined function as follows will convert everything to text and allow for leading zeros which you have not shown. Function ShowAsText(cell) returns string ShowAsText = "'" & cell.text 'single quote within double quotes End Function B1 = ShowAsText(A1) or B1 = personal.xls!ShowAsText(A1) Would suggest formatting the B column as text, but the above function will precede by a single quote so you don't have to format as text before using the fill handle to fill down. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "kailuamike" wrote in message ... Problem: The numbers I am sorting are composed of various numbers of integers, some having four digits, others five or six, and so on. I would like to have them sort by number regardless of number of digits. Will Excel let me do this? example: presently it looks like this: 3004 12001656 530393505 2200498378 and I really want it to sort like this: 12001656 2200498378 3004 530393505 Can this be done? -- kailuamike ------------------------------------------------------------------------ kailuamike's Profile: http://www.excelforum.com/member.php...o&userid=29558 View this thread: http://www.excelforum.com/showthread...hreadid=494495 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Select Names box: Column Sorting - yes, again... | YJ | Contacts | 1 | December 4th, 2005 07:28 PM |
creating a bar graph | Johnfli | General Discussion | 0 | October 26th, 2005 08:16 PM |
Return Count for LAST NonBlank Cell in each Row | Sam via OfficeKB.com | Worksheet Functions | 12 | April 17th, 2005 10:36 PM |
Problem with sorting a column of numbers (descending) - top two cells are blank. | LDH | General Discussion | 2 | August 13th, 2004 08:25 PM |
Design questions | Bruce | Database Design | 15 | June 19th, 2004 12:12 AM |