Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 751
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 66
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,081
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 92
Default 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
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
Changing whole numbers into decimals Dos_III Excel Worksheet Functions 3 January 24th 07 04:58 AM
Sorting Decimals as Numbers NathanP1 Excel Discussion (Misc queries) 1 December 13th 06 07:05 PM
Sorting Numbers with Multiple Decimals (cont.) Intern Ian Excel Discussion (Misc queries) 5 September 21st 05 12:04 AM
Sorting Numbers with Multiple Decimals Intern Ian Excel Discussion (Misc queries) 4 September 17th 05 12:27 AM
Excel: How can I get numbers without decimals ? Wamalapada New Users to Excel 2 April 9th 05 01:43 PM


All times are GMT +1. The time now is 12:11 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"