sorting numbers as text

[jafsonic]

I have a list of topic numbers that I sort via a VBA macro which
basically selects the column and hits the sort data button. The data
is numbers formatted as text and sorts by each digit. I'd really
rather it sort by the value following the decimal point.

For example, if I start with the list:
6.3
6.2
6.10
6.21
6.1

it will sort to:
6.1
6.10
6.2
6.21
6.3

but I'd like it to be:
6.1
6.2
6.3
6.10
6.21

Anybody have any ideas? This has been bugging me for some time.

Thanks.

 

[kimmy]

If you want a quick solution it would be to split the text strings into two
columns (using the Left() and Right() functions), convert the values to
numbers and make your VBA macro sort using these extra columns as keys.

 

[jafsonic]

If you want a quick solution it would be to split the text strings into two
columns (using the Left() and Right() functions), convert the values to
numbers and make your VBA macro sort using these extra columns as keys.

Hmm... maybe that will be ok since it will sort 0.1, 0.2, 0.3, 10, 21.


I'll just have to hope I never go over 6.99.
Is there a way to strip out the decimal so that it will sort 61, 62,
63, 610, 621, 699, 6101 ?

 

[Jim Cone]

=SUBSTITUTE(D3,".","")
--
Jim Cone
San Francisco, USA
http://www.officeletter.com/blink/specialsort.htm



"jafsonic"
<[email protected]>
wrote in message
Thanks Kimmy, but I have one concern.
The Left and Right functions require a specified number of digits.
As you can see from my example, right("6.1",2) will yield ".1",
but a right("6.10",2) will give me "10".
Hmm... maybe that will be ok since it will sort 0.1, 0.2, 0.3, 10, 21.
I'll just have to hope I never go over 6.99.
Is there a way to strip out the decimal so that it will sort 61, 62,
63, 610, 621, 699, 6101 ?

 

[Wang]

use mid() instead of right(),
mid(string, start_position, length), here length is optional. if you omit
it, mid() returns a string from start_position to the string end.

wang

 

[bplumhoff]

Hello,

I suggest to take my UDF, see file sort_chapter.xls:
http://www.bplumhoff.de/html/software.html

HTH,
Bernd

 

[bplumhoff]

Take this helper column:
=INT(--A1)*10+LEN(A1)-FIND(".",A1)--A1-INT(--A1)
Copy down.
Sort by this.

Regards,
Bernd

 

[jafsonic]

I hadn't had a chance to thank you all for the good suggestions.

Thanks