sort "numbers" with decimals
 

I've got chapter numbers in one column which goes: 4.1, 4.2, 4.3...., 4.11,
4.12....
How can I sort it by the numbers on the right of the decimal point instead
of getting: 4.1, 4.10, 4.11, 4.12, ....4.2, 4.20, 4.21, ....

Hello Melissa,

http://www.bplumhoff.de/software/sort_chapter.xls

is a sample spreadsheet with a UDF in it which solves this.

HTH,
Bernd

Insert two Columns at the right of the numbers.
Select the range of numbers i.e. "A:A"
Then go to DATA Text to Columns Delimited
Uncheck all checked and then select "Other and put a "." in the space
provided (without the "")
Then Click Finish.
You'll have on B:B all the data before the point.
In Colum C Row 1 you can put =A1&"."&B1 to get the numbers together
again.

Saludos

NlCO


--
NlCO
------------------------------------------------------------------------
NlCO's Profile: http://www.excelforum.com/member.php...o&userid=26123
View this thread: http://www.excelforum.com/showthread...hreadid=401416

you need to split the values first and then sort by the decimals

to split them use <data <text to columns <delimited <other .
<destination next to your chapter numbers.
then sort by the column with the decimals

have fun


--
bill k


------------------------------------------------------------------------
bill k's Profile: http://www.excelforum.com/member.php...nfo&userid=821
View this thread: http://www.excelforum.com/showthread...hreadid=401416

Another option would be to use:

4.01, 4.02, ...

(or 4.001, 4.002, ... if you can hit triple digits)


Melissa wrote:

I've got chapter numbers in one column which goes: 4.1, 4.2, 4.3...., 4.11,
4.12....
How can I sort it by the numbers on the right of the decimal point instead
of getting: 4.1, 4.10, 4.11, 4.12, ....4.2, 4.20, 4.21, ....

--

Dave Peterson