Sorting numbers with two decimals

[Leslie W.]

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!

 

[vezerid]

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

 

[Beege]

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

 

[Duke Carey]

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

 

[Bernd]

Hello,

Try my UDF sort_chapter:
http://www.bplumhoff.de/software/sort_chapter.xls

Open that file at your own risk.

Regards,
Bernd