Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sorting numbers with two decimals
Good Morning!
I searched through some previous posts and found some interesting answers to different sorting questions, however, I could not find one exactly similar to my situation. I need to sort the following list: 12345.01 20052.01 12345.02.02 17789.02 17789.01 12345.01.03 12345.02 Excel sorts as follows: 12345.01 12345.02 17789.01 17789.02 20052.01 12345.01.03 12345.02.02 This would need to show in the following order: 12345.01 12345.01.03 12345.02 12345.02.02 17789.01 17789.02 20052.01 I understand the reason why I'm getting a funky sort is because the items with two decimal points are being treated as text while the others are being treated as numbers. One way I believe I got Excel 2003 to sort properly was to create a new column, format it to be text (Format Cells Text), then paste the row into the row that was previously formatted as text. My data is saved from a sourse as a .csv file. I tried importing the data, but it still treated some numbers as text. Any ideas? Thanks! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sorting numbers with two decimals
On Jun 1, 3:09 pm, Leslie W.
wrote: Good Morning! I searched through some previous posts and found some interesting answers to different sorting questions, however, I could not find one exactly similar to my situation. I need to sort the following list: 12345.01 20052.01 12345.02.02 17789.02 17789.01 12345.01.03 12345.02 Excel sorts as follows: 12345.01 12345.02 17789.01 17789.02 20052.01 12345.01.03 12345.02.02 This would need to show in the following order: 12345.01 12345.01.03 12345.02 12345.02.02 17789.01 17789.02 20052.01 I understand the reason why I'm getting a funky sort is because the items with two decimal points are being treated as text while the others are being treated as numbers. One way I believe I got Excel 2003 to sort properly was to create a new column, format it to be text (Format Cells Text), then paste the row into the row that was previously formatted as text. My data is saved from a sourse as a .csv file. I tried importing the data, but it still treated some numbers as text. Any ideas? Thanks! The formulas will be simpler if your data have fixed widths, i.e. first "." in position 6 and 2nd "." in 9. Use an auxiliary column in B:B (assume that you start from A2). =LEFT(A2,5)+MID(A2,7,2)/100+IF(MID(A2,9,1)=".",MID(A2,9,2)/10000,0) Sort on column B:B HTH Kostis Vezerides |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sorting numbers with two decimals
Leslie W. wrote:
Good Morning! I searched through some previous posts and found some interesting answers to different sorting questions, however, I could not find one exactly similar to my situation. I need to sort the following list: 12345.01 20052.01 12345.02.02 17789.02 17789.01 12345.01.03 12345.02 Excel sorts as follows: 12345.01 12345.02 17789.01 17789.02 20052.01 12345.01.03 12345.02.02 This would need to show in the following order: 12345.01 12345.01.03 12345.02 12345.02.02 17789.01 17789.02 20052.01 I understand the reason why I'm getting a funky sort is because the items with two decimal points are being treated as text while the others are being treated as numbers. One way I believe I got Excel 2003 to sort properly was to create a new column, format it to be text (Format Cells Text), then paste the row into the row that was previously formatted as text. My data is saved from a sourse as a .csv file. I tried importing the data, but it still treated some numbers as text. Any ideas? Thanks! Here's what I would do. I'd convert your "numbers" into three columns using "text to columns", giving me three columns somewhere else (say, column X, Y and Z) and make sure they were formatted as text, not numbers, then recombine these for your viewing pleasure where they were before by =X2 & Y2 & Z2 Then I would sort using the three separated columns. These look like "numbers" that all should be treated as "text", like zip codes or phone numbers. Just my 2cents Beege |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sorting numbers with two decimals
insert a column to either side of your values, then use this formula in the
new column, copying it down as far as needed. Replace the A1 reference with the address of the first cell in your range =TEXT(A1,"#####.00.00") Now sort the two columns on the new column with formulas "Leslie W." wrote: Good Morning! I searched through some previous posts and found some interesting answers to different sorting questions, however, I could not find one exactly similar to my situation. I need to sort the following list: 12345.01 20052.01 12345.02.02 17789.02 17789.01 12345.01.03 12345.02 Excel sorts as follows: 12345.01 12345.02 17789.01 17789.02 20052.01 12345.01.03 12345.02.02 This would need to show in the following order: 12345.01 12345.01.03 12345.02 12345.02.02 17789.01 17789.02 20052.01 I understand the reason why I'm getting a funky sort is because the items with two decimal points are being treated as text while the others are being treated as numbers. One way I believe I got Excel 2003 to sort properly was to create a new column, format it to be text (Format Cells Text), then paste the row into the row that was previously formatted as text. My data is saved from a sourse as a .csv file. I tried importing the data, but it still treated some numbers as text. Any ideas? Thanks! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sorting numbers with two decimals
Hello,
Try my UDF sort_chapter: http://www.bplumhoff.de/software/sort_chapter.xls Open that file at your own risk. Regards, Bernd |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Changing whole numbers into decimals | Excel Worksheet Functions | |||
Sorting Decimals as Numbers | Excel Discussion (Misc queries) | |||
Sorting Numbers with Multiple Decimals (cont.) | Excel Discussion (Misc queries) | |||
Sorting Numbers with Multiple Decimals | Excel Discussion (Misc queries) | |||
Excel: How can I get numbers without decimals ? | New Users to Excel |